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.

RankIF

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.

You may also like

  • James

    If you add a COUNTIFS, this will remove duplicate values based on the two criteria

    1+SUMPRODUCT(($B$2:$B$12=B2)*($C$2:$C$12>C2))
    +(COUNTIFS($B$2:B2,B2,$C$2:C2,C2)-1)

  • James

    If you want the ranking to be reversed from smallest to largest, change the > symbol to <

    =1+SUMPRODUCT(($B$2:$B$12=B2)*($C$2:$C$12<C2))