Ranking Data with multiple criteria

I'm trying to rank data against multiple criteria. I've seen several similar threads, but I've been unable to figure out how to adapt those threads to my situation. I have only tried ranking by each rating separately because I figured if I could do that, then I should be able to combine the individual rankings into one formula. I've been successful in ranking by A and B, but not C. For some reason I can't get the SMALL function to work properly in excel and I need this column as a tiebreaker.

Data columns are below (I've attached an excel spreadsheet with sample data)
Category Item Rating A Rating B Rating C
1 Item 1 1 1 $73
1 Item 2 1 1 $75
1 Item 3 2 1 $66
1 Item 4 2 2 $198
2 Item 1 1 2 $86
4 Item 1 3 2 $113
4 Item 2 6 3 $251
6 Item 1 6 3 $95
6 Item 2 8 4 $284
10 Item 1 5 4 $458
10 Item 2 8 5 $1,267

I'd like the rankings to be by Category(each category to have a ranking of items within that category). The data should be ranked by "Rating A" in descending order, then "Rating B" by descending order, then by "Rating C" in ascending order to produce a unique rank of items within each category.

The other variation I'd like is to exclude "Rating B" from the ranking and just use "Rating A" descending and "Rating C" ascending.


Thanks,
Paul

Try

=SUMPRODUCT(--($A$5:$A$23=$A5),--(C5<C$5:C$23))+1

copied across 3 columns..

Change the < to > for the 3rd cell.

Then copy these 3 formulas down.

Can you post the expected results?

For top group try:

Please Login or Register  to view this content.

copied down

For bottom group try:

Please Login or Register  to view this content.

copied down