Excel Formula to Rank with Duplicates (3 Examples)

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 they sit.

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 the obtained marks. However, the ascending or descending rank prioritizes earlier Student IDs and considers duplicates as different.

Dataset-Rank Excel Formula with Duplicates

Using Formula to Rank with Duplicates in Excel: 3 Suitable Examples

Method 1: Rank Duplicates by Using RANK and COUNTIF Functions

Excel offers RANK or RANK.EQ functions 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


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.


Comparing the assigned values with arguments



0=descending order

We get a similar scenario like the following picture

normal rank function -Rank Excel Formula with Duplicates

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 the 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 combination of functions such as the following


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

combined function outcome-Rank Excel Formula with Duplicates

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)


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 ), and 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.

Formula insertion

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

Descending order

1.2. With Ascending Order

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


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

Formula insertion

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

ascending order

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.

Extra column-Rank Excel Formula with Duplicates

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


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.

Formula insertion

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.

double criteria

Read More: How to Rank Within Group in Excel

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


$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).


formula insertion

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

descending order

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).


formula insertion

➤ 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.

ascending order

🔄 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.

Modified dataset-Rank Excel Formula with Duplicates

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


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

formula insertion-Rank Excel Formula with Duplicates

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

ignoring blank cells-Rank Excel Formula with Duplicates

Read More: Ranking Data in Excel with Sorting

Download Excel Workbook


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.

Related Articles

<< Go Back to Excel RANK Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

  1. 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 RANK function 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

Leave a reply

Advanced Excel Exercises with Solutions PDF