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.

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

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

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.

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

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 show the ranks from smallest to largest as depicted in the below picture.

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.

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

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

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

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

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

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

Download Excel Workbook

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.

Related Articles

• How to Rank with Ties in Excel
• Rank IF Formula in Excel
• How to Rank in Excel Highest to Lowest
• How to Calculate Top 10 Percent in Excel
• Ranking Based on Multiple Criteria in Excel

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