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.

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.

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.

Here in this example, we have a list of students with their score 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

Quick Navigation

Get my exclusive Excel Productivity Bundle **Worth $20, Absolutely Free**.

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

To understand this we need to break this formula into three parts.

And, please remember SUMPRODUCT is a function which can take arrays even when you haven’t applied a formula as an array.

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.

And in the end, you have to use 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 0 where the subject is not matched.

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

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.

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.

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

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

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

Download this sample file from here to follow along.

If you ask me, I believe SUMPRODUCT 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.

I hope this tip will help you in your work and now, tell me one thing.

*Do you know any other method for create 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.

Related Tips

- SUMPRODUCT IF: With a conditional SUMPRODUCT you can you can multiply and sum those ranges which meet criteria [...]
- Sum Values Between Two Dates: With SUMIFS you can add those two dates as criteria and it works like a range and sum all the [...]
- MAXIF: You can combine MAX and IF to create a formula which can help you to get the max value from a range using [...]
- SUMIF / SUMIFS with an OR Logic: When you combine OR logic with SUMIF/SUMIFS you can sum values using two [...]
- OR Logic in COUNTIF/COUNIFS: When we COUNTIF we can just count cells by specifying a single condition and with COUNIFS we can specify [...]
- Count Cells With Text: In Excel, we have different formulas which we can use to count all the cells where we have text values. But [...]