Rank Excel formula with duplicates is one of the ways to sort data in ascending or descending orders. Excel normally ranks duplicate entries as the same number as it’s identical. However, in this article, we demonstrate ways to rank duplicates with different ranks depending on where it sits.

Let’s say, we have a dataset consisting of *Student Name*, *Student ID,* and their *Obtained Number* in a particular subject (i.e., **Math**). We want **to rank** them depending on obtained marks. However, the ascending or descending rank prioritizes earlier *Student ID*s and considers duplicates as different.

**Table of Contents**hide

**Download Excel Workbook**

**3 Examples of Using Formula to Rank with Duplicates in Excel**

**Method 1: Rank Duplicates by Using RANK and COUNTIF Functions**

Excel offers **RANK** or** RANK.EQ** function to normally rank values without considering duplicates. As a result, the **RANK** or **RANK.EQ** function ranks duplicates by the same number in a dataset. The syntax of the **RANK** function is

`RANK(number,ref,[order])`

The arguments define

**number**; value which rank you want to find

**ref**; array, list, or range of numbers you want the **number** to rank against.

**[order])**; how to rank the number against the ref.** [Optional]**

**0** – or default rank numbers in descending order (i.e., largest to smallest)

**1** – rank numbers in ascending order (i.e., smallest to largest)

🔄 Now, if we use the following formula to rank the numbers in our dataset.

`=RANK(D5,$D$5:$D$14,0)`

Comparing the assigned values with arguments

** ****D5=number**

**$D$5:$D$14=ref**

**0=descending order**

We get a similar scenario like the following picture

After inspecting the outcomes, we can argue that the **RANK** function ranks the duplicate values as the same. But it then counts the next values’ rank with latter numbers (considering how many duplicates are present in the range). For example, the function goes from rank **1** to **3** as there is a duplicate rank **1** and the function masks it as rank **2**. Similarly, the **RANK** function does the same with the latter ranks.

🔄 We can achieve slightly different rank types using a **combined function** such as the following

`=SUM(--(IF(FREQUENCY(D$5:D$14,D$5:D$14),D$5:D$14>D5)))+1`

The formula consists of the **FREQUENCY**, **IF,** and **SUM** functions to insert rank against an array. And the formula ranks the duplicates values with the same rank number without masking any numbers.

We can see from the outcomes that the combined formula ranks duplicate values the same and all the other numbers explicitly.

Since, we know, ranking duplicate values at a separate rank can’t be done with a simple **RANK** function we use combined **RANK** and **COUNTIF** functions to achieve the job.

**1.1. With Descending Order**

➤ To rank duplicates along with the other values, use the below formula in any blank cell (i.e., **E5**)

`=RANK(D5,$D$5:$D$14,0)+COUNTIF($D$5:D5,D5)-1`

In the formula, the **RANK** function has all its arguments as explained earlier in the section. And **COUNTIF** function has a range (i.e., **$D$5:D5** ), criteria (i.e., **D5**). **1** is deducted from the **COUNTIF** result to ensure the ranking begins with** 1**. Otherwise, the formula returns rankings without rank **1**.

➤ Press **ENTER** and Drag the **Fill Handle** to display the ranks in descending order as depicted in the latter image.

**1.2. With Ascending Order**

➤ Similar to the descending order, paste the below formula in any adjacent cell (i.e., **E5**).

` =RANK(D5,$D$5:$D$14,1)+COUNTIF($D$5:D5,D5)-1`

All the arguments inside the formula assign the same values as it does in **1.1. Descending Order** section.

➤ Hit **ENTER** and Drag the **Fill Handle **to appear the ranks smallest to largest as depicted in the below picture.

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

**Similar Readings**

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

**Method 2: Combining RANK and COUNTIFS Functions (with Criteria)**

Our dataset has only one obtained marks column to deal with. There may be an instance that arises where we consider ranking our marks not only obtained marks in a particular subject but also another condition (i.e., **Total Number**). Then we want to rank students by assigning two criteria one is the obtained marks in math and the other is the total obtained number.

For that reason, we modify our dataset by inserting an extra column containing the *Total Number* as shown in the below picture. We are aware of the fact that the obtained numbers in the particular subject are repetitive but total numbers aren’t. And that’s how we can simply rank the duplicates.

**Step 1:** Write the following formula in a cell (i.e., **F5**) beside your raw dataset.

`=RANK($D5,$D$5:$D$14)+COUNTIFS($D$5:$D$14,$D5,$E$5:$E$14,">"&$E5)`

An additional criterion is added through the **COUNTIFS** function. The additional criterion is assigning the total number range (i.e., **$E$5:$E$14**) greater than the first entry (i.e., **$E5**). The formula compares all the entries with themselves and ranks them accordingly.

**Step 2:** Use the** ENTER** key to apply the formula afterward Drag the **Fill Handle** to do as such to the whole range. In a moment all the cells get ranked as shown in the below picture.

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

**Method 3: Merging IF, RANK and COUNTIF Functions**

In the previous methods, we use composite functions to create a formula to deal with the ranking. In this method, we apply a similar approach by combining three functions to create a workable formula of our own.

We run a** logical_test** using the **IF** function and insert values depending on the** logical _test** returns values. The syntax of the **IF** function is

`=IF (logical_test, [value_if_true], [value_if_false])`

And our about to use formula is

`=IF($D5="","",RANK($D5,$D$5:$D$14,0)+COUNTIF($D$5:$D5,$D5)-1)`

**$D5=””**, is the **logical_test** run by the formula and if the test returns **TRUE**, the formula inserts a blank in the formula cell (i.e., **E5**). Otherwise displays the rank as we instructed to do in the **RANK($D5,$D$5:$D$14,0)+COUNTIF($D$5:$D5,$D5)-1** portion of the formula. The [**value_if_false]** formula portion defines all the arguments similarly as it does in the earlier section.

**3.1. With Descending Order**

➤ Type the following formula in any blank cell (i.e., **E5**).

`=IF($D5="","",RANK($D5,$D$5:$D$14,0)+COUNTIF($D$5:$D5,$D5)-1)`

➤ After typing the formula Press **ENTER** then Drag the **Fill Handle** to make the ranks emerge as depicted in the below screenshot.

**3.2. With Ascending Order**

Identical to the descending order, follow the below sequences to rank the numbers in ascending order.

➤ Insert the below formula in one of the cells (i.e., **E5**).

`=IF($D5="","",RANK($D5,$D$5:$D$14,1)+COUNTIF($D$5:$D5,$D5)-1)`

➤ Since you insert the formula, hit the **ENTER** key to apply the formula for the entries. Drag the **Fill Handle** in order to rank all the entries in the dataset.

🔄 Sometimes, we have absent students who don’t appear in the exams. As a result, their obtained number of cells remains empty. These **empty cells** pose various error issues.

For instance, we have some blank entries against the numbers as shown in the following dataset. We want the formula to ignore those blank cells and return the ranks.

➤ Modify the **IF, RANK,** and **COUNTIF** formula as below and insert in any cell (i.e., **E5**)

`=IFERROR(RANK($D7,$D$5:$D$14,1)+COUNTIF($D$5:$D7,$D7)-1,"")`

The **IFERROR** formula displays blank cells in entries where empty cells are referred to, otherwise inserts the ranks.

➤ Apply the **ENTER** key then Drag the **Fill Handle** to bring out all the ranks.

**Read More: ****Ranking Data in Excel with Sorting (3 Quick Methods)**

**Conclusion**

In this article, we demonstrate ways to rank Excel formula with duplicates. However, not many ways are there to achieve such ranking. We use the combination of **RANK**, **COUNTIF**, and **IF** functions to create workable formulas and apply them to fulfill the conditions. Hope these described methods quench your quest. Comment, if you have further queries or have something to add.

Rank in excel

I think it injustice if 3 students obtained same marts and 3 different ranks.

If ranks same then the next two ranks are absent

Hello Jamil Khan,

Thank you for your question. Actually, this is how the

RANKfunction works, that is to say, it ranks the duplicate values in ascending or descending order according to the given argument. Now, to have the same ranks for identical values you can follow Method 1 or download the Excel file that the ExcelDemy team has created.Download the Excel File below.

Ranking Duplicates.xlsx