Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

Conditional Ranking In Excel Using Sumproduct Function

how use sumproduct in excel to create a rank if for conditional ranking

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.

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

Yes, you get it right, it’s SUMPRODUCT.

I’m in love with this function from the last couple of years. And today, in this post I will show you a simple way to rank values with a condition by using SUMPRODUCT.

Just a simple way, no big deal. And, this is a kind of technique which can drive you from beginner to advanced Excel user. So, let’s get started.

And, please download this sample workbook from here to follow along. Here in this workbook, you have a list of students with their score in different subjects.

And now, your 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.

Table of Content

  1. Steps
  2. How does it work?


  • First of all, add a new column at the end of the table and name it “Subject Wise Rank”.

  • Now, 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 that how this formula works. And believe me, you’ll be surprised when you get to know that you have done a magic here with this function.

How this Formula Works to Give us a Conditional Ranking

Let me break this formula into three different parts. And, please note here SUMPRODUCT is a function which can take arrays even when you haven’t applied a formula as an array.

1. Part One: Compare Names

In part one, 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.

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

Now, it will look like this.

Let’s come again, you have a 1 where the subject is matched and 0 where the subject is not matched.

2. Part Two: Check Greater than Values

In part two, you have used (–(B2<$B$2:$B$121)) to check other student’s score which are greater than the Tameka’s score. And, it will return 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 first 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”.

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

Now, it will look like this.

Let’s come again, you have a 1 where the score it greater and 0 score where the score is equal or lower than.

3. Part Three: Multiply Two Arrays

Now, take a deep breath and relax. Slow down your mind and think like this.

At this point, you 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 1 for all the values where the score of the students are greater and 0 if equal or lower.

Now, when sumproduct will multiply 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 than marks from Tameka in Finance.

4. Part Four: Adding + ONE

If you are curious to know about why you have to add 1 in the final formula. Here is the reason.

At this point, you know that the total 9 students are 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.


Sample File

download sample file to learn more about this tips


If you ask me, I believe SUMPRODUCT function is one of the most powerful functions in 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.

Now, tell me one thing, do we really need RANKIF in excel? I don’t think so. And, one more thing I want to ask you. Have you ever tried any other method for this? And, if you have any other method please share with me in the comment box.

Credit: Michael Girvin has inspired me for this post

Related Tutorials

  • Ogundepo Ezekiel Adebayo

    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.

    • Puneet Gogia

      Yup, I can understand.

  • Nishan Rashed

    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.

  • Sergei

    Thank you, Puneet!

  • Gary Evans

    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.

    • Plastic Cup

      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

  • sukant rattawa

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

    • Plastic Cup

      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

  • Gary Evans

    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


  • MF

    Nice use of SUMPRODUCT.

    For sharing, I solve this problem with a very similar approach using COUNTIFs.

    Have a look, hope you like it.