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)))+1The 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)-1The 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)-1All 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
Download Excel Workbook
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
Get FREE Advanced Excel Exercises with Solutions!
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