Hi,
Here is a sample data set :
Sales Rep Amount
Julian 1000
Matt 2000
Matt 2000
Julian 5000
Steph 3500
I'm trying to find say the top 2 sales rep. So the end result would be :
1. Julian 6000
2. Matt 4000
I'm pretty sure I need to be using the LARGE function in an array but not quite sure how to get the rest done. Any ideas ?
Thanks !
I suspect you are looking for a single function rather than setting up a list of the distinct sales reps then doing a sumif based on their sales totals then ranking those?
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Heres a solution using vba
Regards
Sean
Please add to my reputation if you think i helped (click on the star below the post)
Mark threads as "Solved" if you have your answer (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [code]
Please supply a workbook containing example Data: It makes its easier to answer your problem & saves time!
The easiest way without is to use a pivot table.
select insert pivot table, select the whole range of data for table/range
choose your location in either a new or existing worksheet & the cell to add the table.
then select the 2 field names to filter data.
Sean Thomas, thanks for the input but i'm neither looking for a vba solution nor a pivot table solution (which I agree would be an excellent solution).
Sambo Kid, yes exactly I'm trying to work out a single formula either array or that I can drag. I've taken a look at Excelisfun's solution which seems elegant but I can quite adapt to what I'm trying to get at : http://www.youtube.com/watch?v=rKDI-kdBsjY
If your data is in A2:B6 then use this formula in D2 confirmed with CTRL+SHIFT+ENTER and copied down to get the totals in descending order
=IFERROR(LARGE(IF(MATCH(A$2:A$6,A$2:A$6,0)=ROW(A$2:A$6)-ROW(A$2)+1,SUMIF(A$2:A$6,A$2:A$6,B$2:B$6)),ROWS(E$2:E2)),"")
when amounts run out you will get blanks
...then use this formula in D2 copied down to get the matching names (also array entered)
=IF(E2="","",INDEX(A$2:A$6,MATCH(1,IF(SUMIF(A$2:A$6,A$2:A$6,B$2:B$6)=E2,IF(COUNTIF(D$1:D1,A$2:A$6)=0,1)),0)))
Note: D1 should be a blank or a header
That works even if some names have the same total, e.g. I changed the data so that Steph and Matt are equal in total, see attached
Audere est facere
Thanks daddylonglegs ! Formulas work perfectly. A last question, would this formula still work if I choose to use named ranges ?
Yes, with some small changes - if you want named ranges for reps and amounts, for instance, (which should be the same size) then the first formula would be
=IFERROR(LARGE(IF(MATCH(reps,reps,0)=ROW(reps)-MIN(ROW(reps))+1,SUMIF(reps,reps,amounts)),ROWS(E$2:E2)),"")
The ROWS part should refer to the first cell in which the formula is placed
and second formula is then
=IF(E2="","",INDEX(reps,MATCH(1,IF(SUMIF(reps,reps,amounts)=E2,IF(COUNTIF(D$1:D1,reps)=0,1)),0)))
where E2 refers to the cell with the amount and D$1:D1 refers to previous cells in the same column as the formula
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks