Multiple Rank function

  • Thread starter Thread starter dperez6811
  • Start date Start date
D

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
 
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
 
Back
Top