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!
This is a great formula. However, I need to conditionally rank AND uniquely rank each row. Does anyone have any ideas on that?
Admittedly SUMPRODUCT is a powerful formula but in this case would it not be both easier and quicker for large datasets to do it this way?
=1+COUNTIFS($B$2:$B$12,B2,$C$2:$C$12,”>”&C2)
This also makes it very straightforward to involve more complex criteria.
I recently came across the SUMIFS function for applying a multiple criteria SUMIF. I don’t know why I’d never seen it before because it has proved to be a life saver. I imagine the COUNTIFS function must be similarly useful. I’ll check it out and post a blog about the two.
Thanks
You might not have noticed the “IFS” functions because they are new to excel 2007.
The sumproduct formula is a great way to rank. However, are you able to add a tiebreak to the existing formula?
This is AWESOME! I spent ages trying to use an array in the rank function to no avail.
I love SumProduct, but I have a data set that includes “N/A” in some cells so the SumProduct would not rank properly. Then I switched to Ben Sowter’s suggestion of using COUNTIFS and that worked perfectly! For a relatively novice Excel user, though they both perform roughly the same action, the logic of COUNTIFS is easier to understand.
Yes you are right, the series of IFS functions such as COUNTIFS and SUMIFS have been a godsend. Really clear and easily understandable. I dont think they are available in excel versions prior to 2007 though.