Ranking with subsets

Question

I'm trying to rank values and have managed to work out how to sort ties. My data looks at the total number of entries, ranks based on that and if there is a tie it looks to the next column of values to sort them out. However, I have two classes (East and West I've called them) of data within my dataset and want to rank them both separately (but stick to the rules above). So, if I had seven entries, 3 of them West and 4 of the East, I want West to have ranking 1,2,3 based on all the values that lie in that subset and East would have ranking 1,2,3,4. Can you explain what your formula is doing so I can understand how to apply your answer better in the future.

Effectively I'm asking what formula needs to go in achieve my result.

Cheers

Paul

Answer 1

There are a few related ways to do this, most involving SUMPRODUCT. If you don't like the solution below and would like to research other ways/explanations, try searching for "rankif".

The function looks up the Class and Value columns and, for every value in those columns, returns a TRUE or 1 if the current Class is a match AND if its Value is larger than the current Value, False or 0 if otherwise. The SUM adds up all these 1s, and the 1+ is for decoration. Remember to enter as an array formula using Ctrl+Shift+Enter before dragging down.

I used the array formula and SUM above to explain, but the following also works and might even be faster since it's not an array formula. It's the same idea, except we hijack SUMPRODUCT's ability to spit out a single value from an array.

=1+SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8>B2))

EDIT

To extend the rank-if, you could add more subsets to rank by multiplying more conditions:

You can also easily add tiebreakers by adding another SUMPRODUCT to treat the ties as an additional subset:

The first SUMPRODUCT is the 'base rank', while the second SUMPRODUCT is tiebreaker #1.