Use the RANK and COUNTIF combination to get the unique ranking

Dear all,

I used the RANK and COUNTIF combination to get the unique ranking of a range. For example, the formula is: =RANK(A1,$A$1:$A$890,0)+COUNTIF($A$1:A1,A1)-1

To my surprise, the failed to produce the results that I want. I attached a sample file here. Basically, the two ranks of 416 and 418 are not there. My frustrated


A source below really shows the formula should work.

http://www.excelforum.com/excel-gene...-formulas.html