Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Rank based on criteria
How can I get excel to not only do a ranking using the sales of all the
employees, but also a rank by region as well? The real spreadsheet is much larger and would take a lot of time to rank by hand. I would like to have a formula do this, not sorting the list and then filling down. Thanks. Steve |
#2
|
|||
|
|||
Hi Steve
I would head column E as Regions, with North, East, South, West in cells E2:E5 In cell F2 enter =SUMPRODUCT(--($C$2:$C$13=E2),$B$2:$B$13) and copy down through cells F3:F5 In cell G2 enter =RANK(F2,$F$2:$F$5) and copy down through G3:G5 -- Regards Roger Govier "Steve DeBruin" wrote in message ... How can I get excel to not only do a ranking using the sales of all the employees, but also a rank by region as well? The real spreadsheet is much larger and would take a lot of time to rank by hand. I would like to have a formula do this, not sorting the list and then filling down. Thanks. Steve |
#3
|
|||
|
|||
Steve,
Try this =MATCH(B2,LARGE(IF($C$2:$C$13=C2,$B$2:$B$13),ROW(I NDIRECT("1:"&COUNTIF($C$2: $C$13,C2)))),0) it is an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "Steve DeBruin" wrote in message ... How can I get excel to not only do a ranking using the sales of all the employees, but also a rank by region as well? The real spreadsheet is much larger and would take a lot of time to rank by hand. I would like to have a formula do this, not sorting the list and then filling down. Thanks. Steve |
#4
|
|||
|
|||
What? Steve DeBruin is Steved? No greetings from Steved? :-(
You're going to kick yourself man, it is so obvious (when you see it that is) =MATCH(B2,SMALL(IF($C$2:$C$13=C2,$B$2:$B$13),ROW(I NDIRECT("1:"&COUNTIF($C$2: $C$13,C2)))),0) again an array formula -- HTH RP (remove nothere from the email address if mailing direct) "Steve D" wrote in message ... That worked great Bob, thanks a lot. One more question. How would you do a reverse rank? So that the lowest sales amount would be ranked #1 and so on. "Bob Phillips" wrote in message ... Steve, Try this =MATCH(B2,LARGE(IF($C$2:$C$13=C2,$B$2:$B$13),ROW(I NDIRECT("1:"&COUNTIF($C$2: $C$13,C2)))),0) it is an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "Steve DeBruin" wrote in message ... How can I get excel to not only do a ranking using the sales of all the employees, but also a rank by region as well? The real spreadsheet is much larger and would take a lot of time to rank by hand. I would like to have a formula do this, not sorting the list and then filling down. Thanks. Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I rank numbers based on other numbers? | Excel Worksheet Functions | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) | |||
SUMIF in multiple columns based on other criteria in Excel? | Excel Discussion (Misc queries) | |||
counting cells (COUNTIF) based on two or more criteria | Excel Worksheet Functions | |||
Rank() based on category | Excel Worksheet Functions |