First, just do this for me, open your Excel workbook and try to type RANKIF. You will be wondering why there is no function in Excel for conditional ranking.
Yes, there is no one.
Now, just think this way, have you ever faced a situation where you must rank values by using some specific criteria? And if yes, then how do you solve that problem, because you know there is no RANKIF function in Excel?
Not sure?
Let me tell you something, whenever you want to create a conditional ranking based on a specific criterion or category-wise ranking, the best way is to use SUMPRODUCT. Yes, you get it right, it’s SUMPRODUCT.
I’m in love with this function for the last couple of years and today, in this post I will show you a straightforward way to rank values with a condition by using SUMPRODUCT. And this is a technique that can drive you from a beginner to an advanced Excel user.
Want to learn about SUMPRODUCT?
Let’s get started.
Here in this example, we have a list of students with their scores in different subjects. You can download this sample file from here to follow along.
Here our target is to rank all the students in each of the subjects. That means, ranking from first to the last student in each subject like Finance, Operations, and so on, according to their marks
Conditional Formula to use it as RANKIF
- First of all, add a new column at the end of the table and name it “Subject Wise Rank”.
- in the D4 cell, enter this formula =SUMPRODUCT((–(C2=$C$2:$C$121)),(–(B2<$B$2:$B$121)))+1 and hit enter.
- After that, apply that formula to the end of the column, up to the last cell.
Congratulations, you have added subject-wise ranks for the students, and do you believe you took a few seconds?
Isn’t it simple and effective? But the important part is to understand how this formula works. And believe me, you’ll be surprised when you get to know that you have done magic here with this function.
How this Conditional RANKIF Formula works
To understand this, we need to break this formula into three parts. And please remember that SUMPRODUCT is a function that can take arrays even when you haven’t applied a formula as an array.
Part 1: Compare Names
In the first part, you have used (–(C2=$C$2:$C$121))
to compare a subject name with the entire range. And it will return an array in which all those values will be true and matched with the subject name “Finance”.
To check, just edit your formulas in cell D4, select only the first part of the formula and press F9. It will show all the values of the array.
Here all the values which are matched with the subject name from cell D4 are TRUE and the others are FALSE. So the point is, that it has returned a TRUE in the entire array where the subject name is matched.
And in the end, you must use the double minus sign to convert TRUE and FALSE into 1 and 0.
Result from this part of the formula: We have a 1 where the subject is matched and a 0 where the subject is not matched.
Part 2: Check Greater than Values
In the second part, you have used (--(B2<$B$2:$B$121))
to check other students’ scores that are greater than Tameka’s score. And it returns an array in which all the values are TRUE where marks are greater than Tameka.
To check, just edit your formulas in cell D4, select only the second part of the formula and press F9. It will show all the values of the array.
Here all the values which are greater than “24” are TRUE and others are FALSE. So, the point is, that it has returned a TRUE in the entire array where the scores are greater than “24”.
And in the end, you must use the double minus sign to convert TRUE and FALSE into 1 and 0. Now, it will look like this.
Result from this part of the formula: We have a 1 where the score is greater and a 0 score where the score is equal or lower than.
Part 3: Multiply Two Arrays
Now take a deep breath and relax. Slow down your mind and think like this. At this point, we have two different arrays.
- In the first array, you have 1 for all values where the subject is matched and 0 if not matched.
- In the second array, you have one for all the values where the score of the students is greater and zero if equal or lower.
Now, when SUMPRODUCT multiplies these two arrays you will get 1 only for those students whose subject is matched, and the score is greater than Tameka.
Just look at this, there are 9 other students with greater marks than Tameka in Finance.
Part-4: Adding + ONE
If you are curious to know why you need to add 1 in the final formula, then here is the reason for this: At this point, you know that a total of 9 students are there whose marks are greater than Tameka’s.
So, if 9 students are there, Tameka should be in 10th rank. That’s why you need to add 1 at the end of the formula.
Get the Excel File
Conclusion
If you ask me, I believe SUMPRODUCT is one of the most powerful functions in the Excel library and the method we have used above is simple and effective.
With SUMPRODUCT, you don’t need to write long nesting conditional formulas. You just need this magic trick to add conditional ranks. I hope this tip will help you in your work and now, tell me one thing.
Do you know any other method to use the RANKIF?
Please share your views with me in the comment section, I’d love to hear from you, and please don’t forget to share this tip with your friends.
Hi
I have been using this sumproduct formula to rank bids in a tender that we do for several years now but this year I came across a problem which I think is related to the number of rows of day. I have 16799 rows of data and the formula won’t work. It works with 1679 rows but not 16799. Is there a row limit for using this formula?
Found the error – it was not a row limitation error at all but an error value error in one of the cells we were looking at
Thanks heaps for the detailed explanation. This has saved me hours worth of work.
I do have one question and I am hoping you might be able to address.
I have sales figures for the total brand and a separate column for sales for the locations. Some products have no sales at that location, but since we are adding +1 at the end of the formula, these null values are ranked 1 whereas I was hoping to eliminate these from the ranking range. Is there a variation of the formula to eliminate zero values from the ranking range?
Magnificent work, yes Sumproduct was ahead of its time.
Just to point out that when copy pasting the formula at-
“in the D4 cell, enter this formula =SUMPRODUCT((–(C2=$C$2:$C$121)),(–(B2<$B$2:$B$121)))+1 and hit enter."
It pastes only a single subtraction sign (-), rather than two (- -)
To rectify put an extra space between the subtract signs
kind regards Mick Barry
Mum can I have a dog for Christmas.
No you will have a turkey like everyone else.
Magnificent work, yes Sumproduct was ahead of its time.
Just to point out that there is a typo at-
“in the D4 cell, enter this formula =SUMPRODUCT((–(C2=$C$2:$C$121)),(–(B2<$B$2:$B$121)))+1 and hit enter."
There is only a single subtraction sign (-), it should be two (–)
kind regards Mick Barry
Mum can I have a dog for Christmas.
No you will have a turkey like everyone else.
You just saved my day! I’ve been trying to figure out how to do this for the last 6 hours. I have a dataset where I have to rank a list of 150 items by unit sales for each store number in the company. This helped me accomplish what seemed like the impossible. Much gratitude. Thank you. Paul
that’s great to hear, Paul.
If there is an duplicate score, the 2 score will share the same rank, e.g rank 5, the next rank will be rank 6, but per your formula, it will skip rank 6 and go to rank 7.
Can guide the formula if we want same score with same rank as rank no. 5 and the next score will flow to rank no. 6 instead of rank 7.
+1
1. How should I know that how many students are having more number than Mr. Tameka? or before doing the formula how should I know that Mr. Tameka is 9th in position?
2. <24 values became false or zero in 2nd part than how did it find out the 11th to 13th position of Finance group.
X Y Z AA
“Total
Marks” Percentage Result RANK
600
269 44.83 PASSED 1
235 39.17 PROMOTED No Rank
204 34.00 PASSED 4
266 44.33 PROMOTED No Rank
24 04.00 FAILED No Rank
257 42.83 PASSED 2
257 42.83 PASSED 2
Hi,
I am working on above data and used the formula:
=IF(Z10=”PASSED”,SUMPRODUCT((Z10=$Z$10:$Z$79)*(X10<$X$10:$X$79))+1,IF(Z10="","","No Rank"))
But Rank "3" is missing. Formula calculated RANK "2" twice and then because of incremented value it reflected "4" instead of "3". Kindiy check and help. If RANK is same then formula must not increment the RANK value.
Hi, Thank u for this. Just what I needed.
But when I enter it in google sheets it says parse error
Can you help me please?
Thanks for sharing, hero.
Awesome & great article.
Super userful!
Thank you this saved me today!
Thank You!
Hi Puneet,
I downloaded the sample file, added the “Subject Wise Rank” name on cell D1,
Copy the formula and paste on cell D2 of sample File.
Cell D2 Displays: #NAME?
Thanks
Andy
Hi,
If you copied and pasted, just try retyping the — in both areas.
This worked for me:)
Good Luck
-C
Hello,
Thank you for sharing this formula! Can you maybe advice how to adjust formula if some of the scores are the same under group finance (two students lets say received the same grade within certain group)? With current formula the same score within the same group is given the same score instead of ranking them.
Thanks in advance for your help!
=SUMPRODUCT((C2=$C$2:$C$91)*(B2<$B$2:$B$91))+COUNTIFS($B$2:B2,B2,$C$2:C2,C2)
I think this will help..,,
Hi Puneet
I have been using a “copy and paste” version of SUMPRODUCT to do conditional ranking, and while it works fine, I’ve been terrified about having to change it because I didn’t really know how it worked! I had been unable to find a full explanation of exactly what it was doing … until now! Thanks for taking the time to set this out. I finally understand it enough to be able to modify it for different scenarios if required.
Hi Puneet,
We can also use SUMPRODUCT(($C$2:$C$121=C2)*(B2<$B$2:$B$121))+1.
This will help is doing away with the need of double minus sign.
Thanks,
Amit
You could have also used following formula:
SUMPRODUCT(($C$2:$C$121=C2)*(B2<$B$2:$B$121))+1
Have replaced "," comma with * (operator). it reduces requirement of few parentheses
I love you!
Nice use of SUMPRODUCT.
For sharing, I solve this problem with a very similar approach using COUNTIFs.
https://wmfexcel.com/2016/03/12/rank-in-subgroup-rankif/
Have a look, hope you like it.
Hi Plastic cup,
thanks for the reply. I wasn’t clear enough. The “runs” are all in one column. See screenshot as an example..
Regards Gary
https://uploads.disquscdn.com/images/dbe2806f29a86c92e837193a19d2e4b735c586c3fb51ba7419147d3a3b4cc439.jpg
Hi. I have a similar problem.I want to capture price of a product from price list, wherein that product has different level of quality(Ex.- 5 or 3) different range of price has been given in front of each product in price list( price = 5 for quality range(1-10) & Price =6 for quality (10-20) for the same product. the data is huge. i just want to create a single formula for this.
would appreciate any suggestion..
A simple IF() should do the trick. Look at the example. The IF() will look at the Quality level given and substitute in the price. The formula them multiplies that price by the quantity required to give you a total price for each line. All you need to do is enter the formula in the first cell of column C and then double-click the Fill Handle to copy it down the column. As a bonus, I used SUMPRODUCT to show how you can evaluate the subtotals based on the Quality given. SUMPRODUCT is one of the most versatile and useful formulas in an Excel power user’s toolbox. https://uploads.disquscdn.com/images/5672202c29873d7bf4f1a02d8e5ef30d2d0861dd15677ec4887a348cd232617a.png
Brilliant post. I have a similar problem that I can’t solve, maybe you can help.
I have a column that has many runs (1,2,3,4 and 1,2,3 and 1,2,3,4,5,6, Etc.) in it.
I want to rank the runs from the longest to the shortest and can’t see how to do it.
Any ideas would be gratefully received.
To avoid the Circular Reference problem, use two helper columns. In the 1st helper column, use a LEN() formula to calculate the length of the text string in the run cell. The longer the length, the more runs. In the 2nd helper column, use a RANK() formula that will evaluate the adjacent LEN() result against all of the LEN() results and give you a ranking. If you don’t want any ties then modify the LEN() to LEN()+ROW()*1e-12
https://uploads.disquscdn.com/images/e49029e7ee13960afe8ed0d2ca4b37c6dc46bd381962d42ee5ec9ff1be7a71e5.png
Thank you, Puneet!
hi Puneet! the best thing about you that you address you solve problem based on practical scenerios. This why this site is the best for excel learners. One thing I dream about a trick or combination of formula to find the 2nd value / latest value using vlookup rather than getting the first value. Im sure you have solution for this.
I didn’t get the logic for the first time but when I reread it, I got the whole tricks. Thanks Puneet.
Yup, I can understand.