# Using an Excel Formula to Rank with Duplicates – 3 Methods

This is the sample dataset.

### Method 1 – Ranking Duplicates by Using the RANK and COUNTIF Functions

The formula syntax for the RANK function is:

RANK(number,ref,[order])

number; the rankÂ  whose number you want to find

ref; array, list, or range of numbers

[order]); a number that shows how to rank the numbe. [Optional]

0 – or default rank numbers in descending order

1 – rank numbers in ascending order

Using the following formula to rank the numbers in our dataset:

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

Â D5=number

\$D\$5:\$D\$14=ref

0=descending order

This is the output.

Slightly different rank types using a combination of functions can also be used:

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

The FREQUENCY, IF, and SUM functions were used. The formula ranks the duplicate values with the same rank number, displaying all other numbers.

#### 1.1. In Descending Order

• To rank duplicates along withÂ  other values, enter the below formula in a blank cell (E5)
=RANK(D5,\$D\$5:\$D\$14,0)+COUNTIF(\$D\$5:D5,D5)-1

The RANK function has all its arguments. The COUNTIF function has a range (\$D\$5:D5), and criteria (D5). 1 is subtracted 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.

#### 1.2. In Ascending Order

• Enter theÂ  formula below in a blank cell (E5).
Â =RANK(D5,\$D\$5:\$D\$14,1)+COUNTIF(\$D\$5:D5,D5)-1

All the arguments in the formula return the same values as in 1.1. Descending Order .

• Press ENTER and Drag the Fill Handle to see the ranks in ascending order.

### Method 2 – Combining the RANK and COUNTIFS Functions (with Criteria)

This is the sample dataset.

Step 1: Enter the following formula in a blank cell (F5).

=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. It assigns the total number range (\$E\$5:\$E\$14) greater than the first entry (\$E5). The formula compares all the entries and ranks them.

Step 2: Press ENTER and drag the Fill Handle.

This is the output.

Read More: How to Rank Within Group in Excel

### Method 3 – Merging the IF, RANK and COUNTIF Functions

The IF function runs a logical_testÂ  and inserts values depending on the logical _test return values. The syntax of the IF function is:

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

This is the formula:

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

\$D5=””, is the logical_test. If the test returns TRUE, the formula inserts a blank in cell (E5). Otherwise, the RANK(\$D5,\$D\$5:\$D\$14,0)+COUNTIF(\$D\$5:\$D5,\$D5)-1 portion of the formula displays the rank. The [value_if_false] formula portion defines all arguments.

#### 3.1. In Descending Order

• Enter the following formula in any blank cell (E5).
=IF(\$D5="","",RANK(\$D5,\$D\$5:\$D\$14,0)+COUNTIF(\$D\$5:\$D5,\$D5)-1)

• Â Press ENTER and drag the Fill Handle to see the output.

#### 3.2. In Ascending Order

• Enter this formula in a blank cell (E5).
=IF(\$D5="","",RANK(\$D5,\$D\$5:\$D\$14,1)+COUNTIF(\$D\$5:\$D5,\$D5)-1)

• Press ENTER.Â
• Drag the Fill Handle to rank all entries in the dataset.

The following dataset contains blank entries. A formula can ignore them and return the ranks.

• Modify the IF, RANK, and COUNTIF formula as below and enter it in a blank cell (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.

• Press ENTERÂ  and drag the Fill Handle to see all the ranks.

Read More: Ranking Data in Excel with Sorting

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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.