DanielJeff
New Member
- Joined
- Apr 26, 2023
- Messages
- 9
- Office Version
- 365
- Platform
- Windows
Afternoon,
I've been working on a top 10 list so that we can see the top 10 sales by department. I've written 3 formulas to display what i need, but my issue is that they return entries with 0 sales and i dont need to see these.
My knowledge of more complex formulas is quite basic and i've pieced together my formulas from bits i've found on various websites.
Below is a table containing an example of the type of data i've used and underneath that is my table that my formulas produce along with the formulas used for each of the 3 columns. As you're be able to see I've got it show that the formula reads A31 (cell i type the department code in) I need the top 10 for, but it then displays any 0 sales for that code in my table.
I need to be able to change the code and it list anyone with sales in that code. I then need to factor in A28 so that i can change this number from between 1-10 and it display those result, for example if i want to see code FP, but only the top 3 people or 5 people in that code.
Hopefully i've explained that clearly
I've been working on a top 10 list so that we can see the top 10 sales by department. I've written 3 formulas to display what i need, but my issue is that they return entries with 0 sales and i dont need to see these.
My knowledge of more complex formulas is quite basic and i've pieced together my formulas from bits i've found on various websites.
Below is a table containing an example of the type of data i've used and underneath that is my table that my formulas produce along with the formulas used for each of the 3 columns. As you're be able to see I've got it show that the formula reads A31 (cell i type the department code in) I need the top 10 for, but it then displays any 0 sales for that code in my table.
I need to be able to change the code and it list anyone with sales in that code. I then need to factor in A28 so that i can change this number from between 1-10 and it display those result, for example if i want to see code FP, but only the top 3 people or 5 people in that code.
Hopefully i've explained that clearly
| Name | Code | Department | Sales | Total Royalty |
| Kiana Collins | FP | Agrii | 1.000 | £187.00 |
| Miles Frederick | FP | Warehouse | 2.000 | £0.00 |
| Jaycee Best | FP | Front Office | 3.000 | £0.00 |
| Adeline Harrington | FP | Remote | 0.000 | £0.00 |
| Mary James | FP | Machine | 0.000 | £1,500.00 |
| Carolina Todd | LS | Agrii | 1.000 | £0.00 |
| Alexa Foster | LS | Warehouse | 2.000 | £0.00 |
| Kayden Hopkins | LS | Front Office | 3.000 | £0.00 |
| Gabriela Fleming | LS | Remote | 0.000 | £0.00 |
| Andrea Larson | LS | Machine | 0.000 | £230.00 |
| Rafael Thomas | SB | Agrii | 0.000 | £0.00 |
| Elizabeth Miller | SB | Warehouse | 1.000 | £0.00 |
| Russell Ward | SB | Front Office | 2.000 | £0.00 |
| Ariana Beil | SB | Remote | 3.000 | £0.00 |
| Roselyn Turner | SB | Machine | 0.000 | £0.00 |
| Roger Bishop | SW | Agrii | 1.000 | £0.00 |
| Sydney Galvan | SW | Warehouse | 2.000 | £0.00 |
| Kingsley Bruce | SW | Front Office | 3.000 | £0.00 |
| Teresa Maynard | SW | Remote | 0.000 | £0.00 |
| Landry Stokes | SW | Machine | 0.000 | £0.00 |
| Miranda Reilly | WW | Agrii | 1.000 | £0.00 |
| Brooklynn Morgan | WW | Warehouse | 2.000 | £0.00 |
| Harvey Montes | WW | Front Office | 0.000 | £0.00 |
| =INDEX($A$2:$A$24,SMALL(IF(($D$2:$D$24=D27)*($B$2:$B$24=$A$31),ROW($D$2:$D$24)-ROW($D$1)),COUNTIF(D27:$D$27,D27))) | =FILTER(Totals[Department],Totals[Name]=B27,"") | =LARGE(IF($B$2:$B$24=$A$31,$D$2:$D$24),ROW(B27)-ROW(B$26)) | ||
| Top Sales | Jaycee Best | Front Office | 3 | |
| 10 | Miles Frederick | Warehouse | 2 | |
| Kiana Collins | Agrii | 1 | ||
| Department | Adeline Harrington | Remote | 0 | |
| FP | Mary James | Machine | 0 |
