Often you may need to rank items from a dataset based on multiple criteria. More specifically, you have to accomplish this task when there are ties in one column. In this instructive session, I’ll demonstrate 4 cases with the proper explanation of ranking in Excel based on multiple criteria.

**Table of Contents**hide

## Download Practice Workbook

## 4 Cases for Ranking Based on Multiple Criteria in Excel

Let’s introduce today’s dataset where **Scores **of the **Student **in **Math **and **Psychology **are given according to their corresponding **Group**. Here, **D6 **and **D7 **cells are tied in column **D**. So, let’s apply the ranking with consideration of column **E**.

### 1. Using RANK.EQ and COUNTIFS Functions

In the beginning method, I’ll show you the combined use of the **RANK.EQ** **function** and **COUNTIFS function**. To rank based on the two *Scores*, insert the following formula.

`=RANK.EQ($C5,$C$5:$C$15)+COUNTIFS($C$5:$C$15,$C5,$D$5:$D$15,">"&$D5)`

Here, **C5 **and **D5 **are the starting cell of* Score (Math)* i.e. column **C, **and* Score (Psychology)* i.e. column **D **respectively.

⧬ **Formula Explanation:**

- The
**RANK.EQ**function returns the rank number from the**C5:C15**cell range based on the**C5**cell. Unfortunately, it provides the same rank for the duplicate scores (e.g. rank number is 7 for**C6**,**C7**, and**C12**cells). - So, the
**COUNTIFS**function is assigned in descending order (**“>”&$D5) t**o**count duplicate**scores. For example, the function returns 1 for the**C7**cell and 2 for the**C12**cell. - However, when you sum the two outputs i.e. the output
**RANK.EQ**function and the output of the**COUNTIFS**function, you’ll get the unique rank number for all students.

After pressing **ENTER **and using the** Fill Handle** tool, you’ll get the following output.

If you look closely at the above image, you’ll get that the *Rank *for Robert Smith is 7 (look at the **B6:E6** cells) whereas it is 8 for Jim Brown (look at the **B7:E7** cells).

**Read More:** **How to Create an Auto Ranking Table in Excel (with Quick Steps)**

### 2. Ranking Based on Multiple Criteria Using COUNTIF and COUNTIFS Functions

Similarly, you may use the **COUNTIF function** instead of the **RANK.EQ** function.

`=COUNTIF($C$5:$C$15,"<"&$C5)+COUNTIFS($C$5:$C$15,$C5,$D$5:$D$15,"<"&$D5)+1`

Here, I want to rank the scores in ascending order (**“<“&$D5)**.

⧬ **Formula Explanation:**

- The
**COUNTIF**function counts the number of cells having values greater than the corresponding cell (like**C5**for James Smith,**C6**for Jim Brown, and so on). - Lastly, you have to add 1 with the output as the
**COUNTIF**function returns**0**for the smallest values i.e. for the**C13**cell.

So, the output will be as follows.

**Read More:** **Rank IF Formula in Excel (5 Examples)**

**Similar Readings**

**How to Rank with Ties in Excel (5 Simple Ways)****Calculate Rank Percentile in Excel (7 Suitable Examples)****How to Calculate the Top 10 Percent in Excel (4 Ways)**

### 3. Applying RANK and SUMPRODUCT Functions

Also, you can utilize both the **RANK** **function** and **SUMPRODUCT** **function** for ranking the items based on multiple criteria.

Now, look at the following dataset from where you need to rank based on the **GRE Score (Quant)** and **Financial Aid**. But the cell values of **C10 **and **C11 **are tied.

So, insert the following combined formula.

`=RANK(C5,$C$5:$C$15)+SUMPRODUCT(--($C$5:$C$15=$C5),--(D5<$D$5:$D$15))`

⧬ **Formula Explanation:**

- The
**RANK**function returns the rank number from the**$C$5:$C$15**cell range based on the**C5**cell with the duplicates value in the**C10**and**C11**cells (the rank number is**2**). - And, the
**SUMPRODUCT**function finds**0**in case of no tied values. But it returns**1**for the**C10**cell. - Notably, the (
**—**) operator is used to return**1**instead of getting**TRUE**and**0**for**FALSE**. - Thus, you can easily avoid the duplicate rank number using this formula.

Finally, the output will look as follows.

Instead of utilizing the **RANK **function, you can use the **COUNTIF **function. But you have to add **1** in that case.

`=COUNTIF($C$5:$C$15,">"&$C5)+SUMPRODUCT(--($C$5:$C$15=$C5),--(D5<$D$5:$D$15))+1`

Certainly, you’ll get the same output.

**Read More: ****How to Stack Rank Employees in Excel (3 Methods)**

### 4. Ranking with Multiple Criteria by Group

What if you have some common *Groups *in your dataset? For example, the **Science **group covers **C5:C6** and **C11:C12** cells.

Fortunately, you can get the unique rank number dealing with both *Group *and *Score*. We have functions that can help us for ranking in Excel based on multiple criteria on groups.

#### 4.1. Using COUNTIFS Function

Using the **COUNTIFS **function, you can easily rank the *Score *by the assigned *Group *in descending order (**“>”&D5**).

`=COUNTIFS($C$5:$C$15,C5,$D$5:$D$15,">"&D5)+1`

⧬ **Formula Explanation:**

- The
**COUNTIFS($C$5:$C$15,C5)**returns 4 as there are 4 strings available namely*Science*. - And, the
**COUNTIFS($C$5:$C$15,C5,$D$5:$D$15,”>”&D5)**syntax returns**0**for the highest scores (e.g. for the**E6**cell). That’s why you need to add**1**.

Here, the scores are ranked based on the Group separately. For example, Jim Brown (**B6** cell) is ranked 1st though the score of Mary Smith (**B13** cell) is greeted than him.

**Read More:** **How to Rank Within Group in Excel (3 Methods)**

#### 4.2. Using SUMPRODUCT Function

Similarly, you can use the following formula where the **SUMPRODUCT **function is used (ranking in ascending order).

`=SUMPRODUCT((C5=$C$5:$C$15)*($D5<$D$5:$D$15))+1`

⧬ **Formula Explanation:**

- The
**SUMPRODUCT((C5=$C$5:$C$15)**returns**0**. - Besides, the
**SUMPRODUCT((C5=$C$5:$C$15)*($D5<$D$5:$D$15))**finds**2**. But the**SUMPRODUCT**function returns for**E7**cell is**0**as it is the smallest score. So, you need to**1**to avoid such type of error.

**Read More: ****How to Rank Average in Excel (4 Common Scenarios)**

## Conclusion

That’s the end of today’s session. This is how you may accomplish raking in Excel based on multiple criteria. Anyway, if you have any queries or recommendations, please share them in the comments section.

I need your support to find a formula for aging stock to put a value to a range of time based on the remaining quantity and aging from invoiced quantity

Hi NGÂN,

The solution you want will require a combination of some functions like TODAY, COUNTIF, VLOOKUP, etc. Here is a post on our website that will help you.

https://www.exceldemy.com/stock-ageing-analysis-formula-in-excel/

We have several posts related to this topic too.

https://www.exceldemy.com/make-inventory-aging-report-in-excel/

https://www.exceldemy.com/excel-ageing-formula-for-30-60-90-days/

https://www.exceldemy.com/aging-of-accounts-receivable-in-excel/

I hope these articles will help get your job done. If not, please remember that we are just a text away!!

Thank you. Have a good day.

Thanks for providing the guide. How do you rank without duplicates in the case of 4. Ranking with Multiple Criteria by Group? So instead of having duplicate ranks, I want to avoid them without skipping any number. Thanks in advance

Greetings Edward,

Thanks a lot for your Question in our blog post. Now the issue you have is a little bit unclear to me. Can you provide a sample output manually which will contain your desired result? In that way, your problem be more clear to us and in turn it will help us to resolve your problem.