Conditional Ranking in Excel using SUMPRODUCT Function [RANKIF]

First, just do this for me: open your Excel workbook and try to type RANKIF. You will wonder 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’ve been 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.

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

1. First of all, add a new column at the end of the table and name it “Subject Wise Rank”.
   
2. in the D4 cell, enter this formula =SUMPRODUCT((–(C2=$C$2:$C$121)),(–(B2<$B$2:$B$121)))+1 and hit enter.
   
3. 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. Do you believe you took a few seconds?

Isn’t it simple and effective? However, the important part is understanding how this formula works. And believe me, you’ll be surprised when you know that you have done magic here with this function.

How does this Conditional RANKIF Formula Work?

To understand this, we need to break this formula into three parts. 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 that 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.

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

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.

The result from this part of the formula is that we have a 1 where the score is greater and a 0 where the score is equal to 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 one for all the values where the students’ scores are greater and zero if they are equal or lower.

Now, when SUMPRODUCT multiplies these two arrays, you will get 1 only for students whose subjects are matched and whose scores are greater than Tameka.

Just look at this: there are nine 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.

Conclusion

If you ask me, 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 need this magic trick to add conditional ranks. I hope this tip will help you in your work. Now, tell me one thing.

Do you know any other method for using the RANKIF?

Please share your views with me in the comment section. I’d love to hear from you. Also, please don’t forget to share this tip with your friends.