Conditional Ranking in Excel using SUMPRODUCT Function [RANKIF]

First of all just do this for me, open your Excel workbook and try to type RANKIF.

You will be wondered that there is no function in Excel for conditional ranking.

Yes, there is no one.

everytime use sumproduct for conditional ranking

Now, just think this way, have you ever faced a situation where you have to rank values by using some specific criteria?

And if yes, then how you solved 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.

every time use sumproduct for conditional ranking
data table to use to create rank if with sumproduct for conditional ranking

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 as RANKIF

  • First of all, add a new column at the end of the table and name it “Subject Wise Rank”.
add new column to data table to create rank if with sumproduct for conditional ranking
  • Now in the D4 cell, enter this formula =SUMPRODUCT((–(C2=$C$2:$C$121)),(–(B2<$B$2:$B$121)))+1 and hit enter.
add formulas to data table to create rank if with sumproduct for conditional ranking
  • After that, apply that formula to the end of the column, up to the last cell.
drop down formula to data table to create rank if with sumproduct for conditional ranking

Congratulations, you have added subject wise ranks for the students, and do you believe you took a few seconds.

How this Conditional RANKIF Formula works

break down formulas to data table to create rank if with sumproduct for conditional ranking

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 which are matched with the subject name “Finance”.

To check, just edit your formulas in cell D4, select only 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 the cell D4 are TRUE and the others are FALSE.

So the point is, it has returned a TRUE in the entire array where the subject name is matched.

check first part of the formula to data table to create rank if with sumproduct for conditional ranking

And in the end, you have to use the double minus sign to convert TRUE and FALSE into 1 and 0.

check first part of the formula with minus sign to data table to create rank if with sumproduct for conditional ranking

Result from this part of the formula: We have a 1 where the subject is matched and 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 student's score which is greater than the 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 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, it has returned a TRUE in the entire array where the scores are greater than "24".

check second part of the formula to data table to create rank if with sumproduct for conditional ranking min

And in the end, you have to use the double minus sign to convert TRUE and FALSE into 1 and 0. Now, it will look like this.

check second part of the formula with minus sign to data table to create rank if with sumproduct for conditional ranking

Result from this part of the formula: We have a 1 where the score it greater and 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.

  1. In the first array, you have 1 for all values where the subject is matched and 0 if not matched.
  2. In the second array, you have 1 for all the values where the score of the students are greater and 0 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.

check both parts of the formula to data table to create rank if with sumproduct for conditional ranking

Just look at this, there are 9 other students with greater than marks from Tameka in Finance.

number of students have more marks check with the formula to data table to create rank if with sumproduct for conditional ranking

Part-4: Adding + ONE

If you are curious to know about 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 is there whose marks are greater than Tameka. So, if 9 students are there then Tameka should be on 10th rank.

That's why you need to add 1 at the end of the formula.

final with the formula to data table to create rank if with sumproduct for conditional ranking

Sample File

Conclusion

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube

15 thoughts

Leave a Comment

Your email address will not be published.

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

  2. 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.

  3. 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

  4. 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

  5. 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

  6. 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.

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

  8. I didn’t get the logic for the first time but when I reread it, I got the whole tricks. Thanks Puneet.