Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking based on sum across multiple locations
I'm trying to get the top ten ranking locations based on the sum of
the sales. The real list is 1000s of entries so here is an example of what it looks like: Location # Sales 1 10 2 20 2 10 2 30 3 50 4 200 4 10 5 20 6 10 6 100 6 20 7 10 The end result would look like this: 4 210 6 130 2 60 3 50 5 20 1 10 7 10 How do I do this in a systematic way with a formula? Thanks for your help in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking based on sum across multiple locations
One way
The assumes your locations in Column A sales in column B Put another list of locations somewhere, in this case I used column C starting in C1 then this formula in D1 =SUMPRODUCT(($A$1:$A$12=C1)*($B$1:$B$12)) Drag down to the same length as column C The select columns C & D and sort on D descending Mike Drag this "Ryan" wrote: I'm trying to get the top ten ranking locations based on the sum of the sales. The real list is 1000s of entries so here is an example of what it looks like: Location # Sales 1 10 2 20 2 10 2 30 3 50 4 200 4 10 5 20 6 10 6 100 6 20 7 10 The end result would look like this: 4 210 6 130 2 60 3 50 5 20 1 10 7 10 How do I do this in a systematic way with a formula? Thanks for your help in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save document to multiple locations | New Users to Excel | |||
save to multiple locations simultaneously | Excel Discussion (Misc queries) | |||
AutoFill pattern based on cell locations | Excel Discussion (Misc queries) | |||
Ranking based on two columns | Excel Worksheet Functions | |||
How do I advance filter to multiple locations at once? | Excel Worksheet Functions |