Ranking without Repetitive Ranks

January 25, 2002 - by Juan Pablo Gonzalez

Here's a question I see often.

I'm trying to use the RANK function to sort the values in Column A, but, the problem arises when I have two or more cells with the same value. Then, the RANK also repeats some positions. For example, if I have, 50, 60, 62, 62, 67 in Column A I would get 5,4,2,2,1 in Column B. Is there a way I can get 5,4,2,3,1?

To do this you need a combination of RANK and COUNTIF as follows. If the data is in A2:A10, the formula in B2 would be:

=RANK(A2,$A$2:$A$10)+COUNTIF($A$2:A2,A2)-1

and drag down till B10.