Multiple Rank function

[dperez6811]

I need a formula that will rank on two columns. So with the below example I
want to rank by Column 1 first, then by 2. So the answer would be as follows
in the below table. If there is a way to make it ascending and descending
rank, that would be helpful to know as well.

Column 1 Column 2 Formula answer
a 1 1
b 2 1
a 3 2
b 4 2

Thanks in advance

 

[Bernie Deitrick]

For your example table, with data in A2:B5, in Cell C2 enter:

=1+SUMPRODUCT(($A$2:$A$5=A2)*($B$2:$B$5<B2))

and copy down.

This will give ties the same rank, with the next rank number skipped.

HTH,
Bernie
MS Excel MVP