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

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

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)

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

=IF(F11=”fail”,”-“,RANK(E11,$E$6:$E$13))

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
BUT
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

Brilliant!!!

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?

Pingback: Ranking per klasse - Worksheet.nl()