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 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 IDs and considers duplicates as different.

Dataset-Rank Excel Formula with Duplicates


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

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

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)

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

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

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

Formula insertion

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

ascending order

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


Similar Readings


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.

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

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

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

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

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)

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

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:


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

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo