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 Conditional Ranking in Excel   RankIF using SumProduct

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.

Share the Love
Get Blog Updates
poweredby Conditional Ranking in Excel   RankIF using SumProduct

Excel Geeky Corner

  • laure

    Thank you very much for this very useful function. Really great!!

  • admin

    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.

  • Mike

    This was a total lifesaver! Thanks so much for posting!

  • Blink

    This is a great formula. However, I need to conditionally rank AND uniquely rank each row. Does anyone have any ideas on that?

  • http://iu.qs.com Ben Sowter

    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.

  • http://whatapalaver.co.uk/ admin

    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

  • Excel Fan

    You might not have noticed the “IFS” functions because they are new to excel 2007.

  • Jordan

    The sumproduct formula is a great way to rank. However, are you able to add a tiebreak to the existing formula?

  • Tim

    This is AWESOME! I spent ages trying to use an array in the rank function to no avail.

  • Jared

    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.

  • Admin

    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.

  • Angie

    Amazing! What a great help! Thank you so much!!!

  • Matt

    Soooo useful! Exactly what I needed and I would never have come up with that myself. Thanks a lot!

  • Thang

    i tried it, but have some bug
    if have 2 ranks = 1, so next rank will be 3 not 2

  • Paul R

    Excellent! you have just saved me hours of work splitting out tables of vehicle registration data into separate sheets for indifidual markets.. I have no idea how it works (being considerably older than the personal computer) but it is a fabulous piece of “cargo code”
    Thanks

  • Pingback: Ranking per klasse - Worksheet.nl

  • Paula

    This is a lifesaver! THANK YOU, THANK YOU, THANK YOU!!!

  • Chris

    I’d been working with the SumProduct formulas and they worked for most of my calculations just fine.
    Ben Sowter’s suggestion of using the =1+COUNTIFS($B$2:$B$12,B2,$C$2:$C$12,”>”&C2) work to fix one of my other issues.
    Thanks for the help one and all

  • Amol

    Brilliantly written

  • CaptainJun

    Thanks for the help!!!!

  • Katy

    Another option, if you are able to sort your data first: Have it look to the column above to see if the data is the same. If so, add 1, if not, start over with a rank of 1.

    =IF(C3=C2, B2+1, 1)

  • harkoliar

    Exactly what I need. Thank you.

  • Cool Moxie

    You’re welcome—
    Sent from Mailbox for iPhone

  • Joshr

    Incredible easy solution. Also it’s interesting to count the specific records (values less then C2) with a “sum” formula. Here’s another workaround:
    =1+COUNTIFS($B$2:$B$12;B2;$C$2:$C$12;”>”&C2)

  • Cool Moxie

    Thanks for your input.

  • Esther

    Wow…. Needed this for a very long time!

  • Bdoo

    GENIUS!

  • Chunk

    Love this. Very simple and elegant. I then divided by a countif($B$2:$B$12,B2) and essentially got percentrankIF (to be precise percentrankIF.inc) out of it. (you have to leave out the “1+” at the beginning)

  • Cool Moxie

    Great suggestion, thanks for sharing your idea. —
    Sent from Mailbox

  • Lucky

    Can you please tell me if 2 values are same we will get same rank. Instead I want the one in the first row to have higher rank than other? Thanks in Advance

  • Henry Hale

    Just wanted to thank you for this guide. Simple, easy to understand and gave me exactly what I was looking for. Thank you.

  • Greg

    Thank you very much!

  • Paul

    Thanks. This was very helpful!

Excel Geeky Corner