How to use the RANK and SUMPRODUCT functions in Excel with COUNTIF

By specifying similar ranks for many of the same items, the RANK.EQ function ignores numbers in the overall series. To avoid this omission, use the SUMPRODUCT function with the COUNTIF function. This combination sorts duplicates without skipping numbers.

Using the RANK function in combination with the COUNTIF . function

The RANK.EQ function can be used in conjunction with the COUNTIF function to stop skipping numbers, but it will also ignore rank duplication. Although no number is repeated twice, two students with the same number (%) may be ranked differently.

To better understand this, see how RANK.EQ works in conjunction with COUNTIF. The formula has the following form:

=RANK.EQ(B2,$B$2:$B$11.0)+COUNTIF($B$2:B2,B2)-1

Implementing this formula solves the problem of ignoring numbers.

How to use the RANK and SUMPRODUCT functions in Excel with COUNTIF Picture 1

There is no overlap in the ranks above. But, James Walker and Gillian Tillman, two people in the same rank, are now ranked in different positions.

Thus, using RANK.EQ with COUNTIF solved half of the problem, but did not produce the desired results.

Using the SUMPRODUCT function with the COUNTIF . function

To rank the students in the list by assigning the same ranks to equivalent percentages without omitting any numbers, you can use the SUMPRODUCT function with COUNTIF.

Take a look at the formula below:

How to use the RANK and SUMPRODUCT functions in Excel with COUNTIF Picture 2

The formula seems complicated, but it's the best way to rank items correctly. This way you can achieve the desired result with rank overlap and no numbers being ignored.

While giving results to your students, you can directly use the SUMPRODUCT formula as a substitute for the RANK function. To calculate non-duplicate ratings, you can use the RANK.EQ function alone or with the COUNTIF function.

How to use the RANK and SUMPRODUCT functions in Excel with COUNTIF Picture 3

Change the order of the final result

On the Data tab, click the Sort and Filter group and choose ascending order to sort the ranking.

How to use the RANK and SUMPRODUCT functions in Excel with COUNTIF Picture 4

Compare the results in three rows side by side to better understand how each method of ranking items works.

Hope you are succesful.

Isabella Humphrey

Update 24 May 2021