Skip to content

Formulas to Rank Items with One or more Criteria in Excel!

Listen to this article

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:

Formulas to Rank ItemsPin
Formulas to Rank Items

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.


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.

Score boardPin
Score board

Use the below-mentioned formula in any cell where you want to get the output result.


Enter the formulaPin
Enter the formula

Once you have entered the formula, click on the Enter button to execute it and get the result.

Hit the Enter buttonPin
Hit the Enter button

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.
Age and WeightPin
Age and Weight
  • Apply the below-given formula to get the result on your spreadsheet.


Type the formulaPin
Type the formula
  • 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.
Fill remaining cellsPin
Fill remaining cells


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:


Leave a Reply

Your email address will not be published. Required fields are marked *

Share to...