Excel has a RANK function, so you can calculate the rank of each number that stands in a list in your spreadsheet. Instead of using the function, we can apply the formula to get it quickly. In this tutorial, we will discuss the simple formula to rank the items in a list using one or more criteria in Excel. Get an official version of MS Excel from the following link:
https://www.microsoft.com/en-in/microsoft-365/excel
Jump To
General Formula
Here we come up with a simple formula to rank the values in a range based on the given conditions on your worksheet.
=COUNTIFS(criteria_range,criteria,values,”>”&value)+1
Syntax Explanations
- COUNTIFS – The Excel COUNTIFS Function will help count the number of cells that meet multiple criteria or conditions.
- Criteria_range – It specifies the criteria range.
- Values – These values represent the input values from your worksheet.
- Comma symbol (,) – It is a separator that helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
How to Apply Formula to Rank Values in a Range?
In order to rank the students based on their total scores, we can use an Excel formula. This formula can make use of the COUNTIFS function of Excel. As we said earlier, the COUNTIFS function returns the values based on multiple criteria that we have given in the formula.
For instance, we are going to rank the students based on their total scores in the examination. The following screenshot will show you the scoreboard of students. We need to get their ranks of them based on some conditions.
Use the below-mentioned formula in any cell where you want to get the output result.
=COUNTIFS($C$3:$C$10,C3,$D$3:$D$10,”>”&D3)+1
Once you have entered the formula, click on the Enter button to execute it and get the result.
In order to fill the remaining cells, you just need to drag down the first output cell. It will auto-fill the values, as shown in the below image.
If the above-given scenario is not enough to understand this formula and want to learn this with a simple example, kindly move to the following section.
Alternate Example
Here we come up with a simple example to know how to apply this Excel formula in the spreadsheet.
- In the below screenshot, we have given the weight details of the group of people. Here, we have taken two age groups “25” and “40.”
- In order to rank them based on their weights, we can use the COUNTIFS function with multiple criteria.
- Apply the below-given formula to get the result on your spreadsheet.
=COUNTIFS($C$3:$C$10,C3,$D$3:$D$10,”>”&D3)+1
- You have to press the Enter button to get the result.
- Now, you can auto-fill the remaining cells by drag-down the first resulting cell in the worksheet.
Wrap-Up
Here we have described the simple formula to rank the items in a list using one or more criteria in Excel from this tutorial. Hope you like this article. Please share your query below in the comment box. We will assist you. Click here to know more about AawExcel and Excel Formulas!
Video Tutorial
The following video will show you how to apply an Excel formula to tank items in a list using one or more conditions.
Further References:
- How to Use Excel FLOOR.MATH Function?
- How to Use Excel FLOOR Function?
- How to Use Excel CEILING.PRECISE Function?
- How to Use Excel CEILING Function?
- How to Use Excel GCD Function?
