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:


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

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

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


    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.

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

  • Pingback: Ranking per klasse -

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

  • Cool Moxie

    Thanks for your input.

  • Esther

    Wow…. Needed this for a very long time!

  • Bdoo


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



  • Nikki

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


  • CSF

    This is so clever, thanks.

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


  • Sathish Kumar

    hi i am calculate for rank formula but anybody fail they was not show the rank but they was calclating rank value ant that was skip the few rank values


  • Yousaf Sipra

    Hello all.
    Please help
    I am making a student result card for grade 10.
    I need to give class positions to each student base on their percentages
    A student who has passed all subjects should get a higher position as compared to the student who has higher percentage but has failed in one subject.
    For example
    I have 5 students in grade 10
    John 78% all passed
    Jim 56 % 1 subject fail
    Ron 51% all passed
    Jay 67% 2 subejcts failed
    Kile 48% all passed

    Now if i give Positions to students it should be like this
    John 1st
    Ron 2nd
    Kile 3rd
    Jim 4th
    Jay 5th

  • Riles

    array function {=SUM(IF($D$2:$D$410>=D2,CHOOSE({1},IF($B$2:$B$410=B2,1,0)),0))}

  • Riles

    you would have to make an equation to calculate this. if you treat a failure like a demerit and detract it from the original percentage you can do this fairly easily.

    for instance if you detracted 20 points for each failure john would lose none, jim would lose 20 and so on. this would give you final scores for each of:

    John – 78
    ron – 51
    kile – 48
    jim – 36
    jay – 27

    this would achieve your ranking

  • Yousaf Sipra

    Thanks alot
    But how do i make an equation for this. I mean how to add points.
    What i tried was that i calculated each students subject wise percentage than i used count if statement to count the subjects which had below 33% percentage.
    But i failed. The main idea was that if count is zero meaning jhon didnt fail in any subject, if count is 1 means jhon failed 1 subect and be treated separatly. Help if u can

  • Jennifer Freer

    This formula is so helpful although can anybody advise how to calculate the rank where two values are the same and I can use another column to help to sort. E.g If PROC 1 above had 2 costs of $289 but I had another column that had ‘time taken’ in and I wanted to rank the cost then the time taken so I didn’t end up with two cells ranked ‘1’…..

  • Robert Wilson

    Thank you so much, been using rank etc for years and found this page first when looking for something better, it took me less than 2 minutes to implement it and it works so much better than I expected. I got it rank 2 teams — =IF(D4=”A-Team”,”A-Team – “&1+SUMPRODUCT(($D:$D=”A-Team”)*($E:$EE4)),””)) — and then give the top 2 and bottom 2 of each team — =INDEX($B:$B,MATCH(“A-Team – 9”,$F:$F,0)) — Thank you for such an inspired formula

  • Alex Goldstein

    This is a great formula. Can you advise on how to do a reverse rank (the lowest price being a 1, and the highest a 4 in your example above)?

  • Jakub Wróbel


  • Jack

    I need this for rank.avg so i can use it for spearmans rank, anybody know how to do this by any chance please?

  • Atul Kathuria

    this is so damn smart!