A.N.M. Mohaimen Shanto
In Microsoft Excel, ranking multiple values or numbers is an everyday task. You will face various instances where you may need to rank items from a dataset. Your ranking may contain single criteria or multiple criteria. Either way, you can calculate the ranks. In this tutorial, you will learn to use the Rank IF formula in Excel with suitable examples and proper illustrations. So, stay tuned.
Download Practice Workbook
Download this practice workbook.
Excel Rank Function
Now, to rank multiple items, there is a built-in Excel RANK Function. This function basically ranks multiple values based on a column that you specify. You can sort the values in ascending or descending order. It is the most straightforward approach to designate a number’s comparative place in a list of numbers that sort the list in descending (from largest to smallest) or ascending ranking (from smallest to largest).
What it does is the following:
As you can see, the RANK function ranks the students based on the Marks. You can sort this by ascending or descending order. But, you can clearly see there are no conditions to rank items.
There are many ways to rank items using this function. To know more about this, read: How to Use RANK Function in Excel (With 5 Examples).
Is it Possible to Rank If Conditions Applied with a Single Function?
Here are the critical questions. Can we rank numbers, values, and items based on single or multiple criteria simply using the RANK function? Or is there a RANKIF function that exists? Unfortunately, No. There is no RANKIF function in Excel. To create a ranking, we use some other functions.
RANKIF is basically a conditional rank. To rank multiple values based on criteria, we use the COUNTIFS function and the SUMPRODUCT function of Excel. These functions will provide you with the same outputs that you are expecting from this article.
In the later sections, we will discuss them in detail.
5 Examples of Rank If Formulas in Excel
In the upcoming sections, we will provide you with five practical examples of Rank IF formulas to implement in your Excel workbook. These examples will help you grab a better idea to rank items. We recommend you learn and apply all of these methods to have a better understanding of this topic.
1. Rank Students’ Marks IF Matches the Group
In this example, we will rank some students’ marks based on their subject group. We are using the COUNTIFS function here to rank numbers.
The Generic formula we are going to use:
=COUNTIFS(criteria_range,criteria,values,”>”&value)+1
Take a look at the following dataset:
Now, follow these steps to create a rank based on the groups.
📌 Steps
- First, type the following formula in Cell E5:
=COUNTIFS($C$5:$C$13,C5,$D$5:$D$13,">"&D5)+1
- After that, press Enter and drag the Fill handle icon over the range of cells E6:E13.
As you can see, our formula is successful to create a rank based on the group in Excel. So, it is working like the Rank IF formula to rank items.
🔎 How Did the Formula Work?
The COUNTIFS function executes a conditional count utilizing multiple criteria. We entered those conditions as a criteria range.
=COUNTIFS(C5:C13,C5)
This function returns three because there are three science groups.
Now, the second criteria are the following:
=COUNTIFS($C$5:$C$13,C5,$D$5:$D$13,”>”&D5)
This function searches the current marks is greater than other marks or not.
=COUNTIFS($C$5:$C$13,C5,$D$5:$D$13,”>”&D5)+1
Here, we are adding 1 to this equation. Because when the marks is the highest in that group it will return 0. But we can’t rank starting from zero. So, we added plus 1 to start the ranking from 1.
Remove Duplicates
Here comes a catch. If you have the same marks for two same students, it will rank both of them to 1. But, it will create a rank of the next item to 3. So, we will have a ranking of 1,1,3. To remove this confusion, just type the following formula:
=(COUNTIFS($C$5:$C$13,C5)+1-(COUNTIFS($C$5:$C$13,C5,$D$5:$D$13,">"&D5)+1)-(COUNTIFS($C$5:$C$13,C5,$D$5:$D$13,"<"&D5)+1))/2
If there are no duplicates in your dataset, it will return 0.
Read More: How to Rank Within Group in Excel (3 Methods)
2. Reverse the Rank IF Formula in Excel
Now, the previous example we showed was in descending order. That means it calculates the rank from largest to smallest. You can easily reverse this formula. Just make a simple tweak in the formula. Change the greater than (>) operator to smaller than (<) operator.
The generic formula:
=COUNTIFS(criteria_range,criteria,values,”<“&value)+1
Follow these steps to see the changes.
📌 Steps
- First, type the following formula in Cell E5:
=COUNTIFS($C$5:$C$13,C5,$D$5:$D$13,"<"&D5)+1
- After that, press Enter and drag the Fill handle icon over the range of cells E6:E13.
As you can see, we are successful to create a rank based on criteria in descending order in Excel.
3. Use of Rank IF Formula for Sales Based on Product
In this example, we are also using the COUNTIFS function to calculate rank items based on multiple criteria. If you have read the previous examples, it will be an easy task for you. Please read the previous examples to understand them better.
Take a look at the dataset:
You can see, we have some salespersons’ selling products and their total sales. This dataset is slightly different from the previous one. Here, we will calculate rank based on the products. But, the products are scattered in the dataset. They are not in groups like the previous one.
📌 Steps
- At first, type the following formula in Cell E5:
=COUNTIFS($C$5:$C$18,C5,$D$5:$D$18,">"&D5)+1
- Now, press Enter and drag the Fill handle icon over the range of cells E6:E13.
Finally, we are successful to create a Rank IF formula in Excel with the COUNTIFS function.
Similar Readings
- How to Create a Ranking Graph in Excel (5 Methods)
- Stack Rank Employees in Excel (3 Methods)
- How to Rank Average in Excel (4 Common Scenarios)
- How to Calculate Rank Percentile in Excel (7 Suitable Examples)
4. Rank IF Formula to Rank Sales Based on Quarters
We are using the same formula here as the earlier one. But, here we are going to perform this at a table. Now, an Excel table allows you to calculate various operations over a dataset.
Take a look at the dataset:
Here, we have some sales data of quarters in a year. We will create ranks based on the criteria “Quarter”.
📌 Steps
- Select the whole dataset first.
- Then, press Ctrl+T on your keyboard to transform it into a table.
- After that, type the following formula in Cell E5:
=COUNTIFS($C$5:$C$16,C5,$D$5:$D$16,">"&D5)+1
- After that, press Enter and drag the Fill handle icon over the range of cells E6:E16.
- Now, you can perform Sorting or Filtering to see the best performing month and least performing month in the quarter. It will sort or filter your ranks.
So, in this way you can create an Excel Rank IF formula to calculate the ranks of a particular dataset for multiple criteria.
5. Rank IF Formula Using the SUMPRODUCT Function in Excel
Now, you can create ranks based on a condition using the SUMPRODUCT function in Excel. It will also work like a Rank IF formula to create ranks based on single or multiple criteria.
The Generic formula we are going to use:
=SUMPRODUCT((criteria_range=criteria)*(value>values))+1
Take a look at the following dataset:
Here, we have some employees data. We will calculate the ranks of their salaries based on Department salaries. The process is similar. We will just use the SUMPRODUCT function.
📌 Steps
- At first, type the following formula in Cell E5:
=SUMPRODUCT(($C$5:$C$18=C5)*(D5>$D$5:$D$18))+1
- Next, press Enter and drag the Fill handle icon over the range of cells E6:E13.
Using the color grading, we tried to differentiate the ranking based on the departments. This Excel formula will work like the Rank If formula that you wanted.
🔎 How Did the Formula Work?
The SUMPRODUCT takes one or more arrays as an argument, multiplies the corresponding values of all the arrays, and then returns the sum of the products. We entered those conditions as a criteria range.
=($C$5:$C$18=C5)
It checks the whole column and finds the match. It returns an array. If there are any matches, it returns TRUE and returns FALSE for non-matching values.
Now, the second criteria are the following:
=(D5>$D$5:$D$18)
It checks the salary. Sorts them in descending order. Returns TRUE for the salaries greater than or equal to D5, FALSE otherwise. To sort it in ascending order, change the greater than symbol (D5<$D$5:$D$18).
=SUMPRODUCT(($C$5:$C$18=C5)*(D5>$D$5:$D$18))+1
Finally, the SUMPRODUCT function sums up the values of the 1’s and 0’s array. It returns 0 for the largest number of each group. And we added 1 to the outcome to start ranking with 1.
💬 Things to Remember
✎ We demonstrated this article in descending order(largest to smallest). You can change the ranking order according to your need.
✎ Don’t forget to add 1 to the formula. Otherwise, it will create ranks starting from 0.
✎ Any Rank procedure in Excel works only for numeric values. It should be positive and negative numbers, zeros, date, and time values. It neglects the non-numeric values.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge about the Rank IF formula in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!
