How to Use the RANK and SUMPRODUCT Functions in Excel with

This practical guide walks through how to use the RANK and SUMPRODUCT functions in Excel with, explains the main requirements, and highlights common issues that may appear along the way.

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.

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:

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.

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.

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

Hope you are succesful.