Frequency function to correct ranking instead of countif

[Scottlarock]

Unresolved Cross post from :

http://www.mrexcel.com/forum/excel-q...d-countif.html

Hello,
I have been using countif to rank numbers across +20 000 rows (+100 000 rows before data cleanup). Needless to say, this countif function is slowing down my model to a point it even crashes my excel. To fix this, I am trying to find a way to use the frequency function.

Below is an example of the desired results using countif in column C :

In C2, I use the classic : =COUNTIF(B$2:B2,B2)-1, which I copy down to the last row. (sorry but the paste and format functionalities on this site are not helping)

A B C D
1 values rank rank adjustment real rank
2 100 7 0 7
3 200 6 0 6
4 300 5 0 5
5 420 2 0 2
6 500 1 0 1
7 420 2 1 3
8 310 4 0 4



Now for the frequency version, the following forumula is wrong, but I understand it needs to look a little something like this, in C2 :

{=SUM(IF(FREQUENCY(IF(B$2:B2<>"",IF(B$2:B2=B2,MATCH("~"&B$2:B2,B$2:B2&"",0))),ROW(B$2:B2)-ROW(B$2)+1),1))-1}


Tons of thanks in advance for your kind help !

Scott