Conditional Ranking in Excel – RankIF using SumProduct
I was hunting around for a RANKIF function yesterday so I could rank a load of hospitals in terms of their cost per procedure.
Unfortunately RANKIF isn’t one of the available functions but I managed to achieve the same effect by utilising the SUMPRODUCT function.
I wrote a SUMPRODUCT tutorial some time ago indicating how the powerful function can act as a multi conditioned SUMIF formula but it seems it can also act as a multi conditioned RANKIF formula as well.
The formula I used to achieve the above ranking was:
=1+SUMPRODUCT(($B$2:$B$12=B2)*($C$2:$C$12>C2))
Copied down the column it will return the number of organisations for a given procedure which have costs higher than the selected row. Adding 1 to the result just ensures that the highest cost organisation starts with a rank of 1 rather than 0.
Thank you very much for this very useful function. Really great!!
I just repeated my own ranking technique and got loads of #VALUE errors which screwed me up for a while.
If you are getting a #VALUE error with SUMPRODUCT it implies that your data in column C (the values to be summed) are not all numeric values.
They may be text values that look like numbers or “” if you have a conditional formula in that column. In my case there turned out to be a spurious #VALUE error in column C.
Problem solved.
This was a total lifesaver! Thanks so much for posting!