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 Tutorial

  • 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!

  • KGS

    Thanks! This just saved me a lot of time!

  • IBRAHIM

    THNX BUT NOT EFFECTIVE FOR DUPLICATED COST WITH SAME PROCEDURE

  • Nikki

    did you ever work this out. It’s exactly what I am looking to do

    Nikki

  • CSF

    This is so clever, thanks.

  • http://portable-tools.blogspot.com kanima

    easy excel rank
    very helpful article
    thanks ya

  • Airat Araslanov

    It’s kind of smart but you don’t really need SUMPRODUCT for this.
    If you think about definition of RANK – all you need to do is to count how many entries have bigger value for your subset. You can do it with COUNTIFS

    The below formula would do the trick for specified example:

    =1+COUNTIFS($B$2:$B$12;B2;$C$2:$C$12;”>”&C2)

Excel Tutorial