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

You should read it

May be interested

How to lock, not edit PowerPoint files
this article will guide you to lock powerpoint files with a password to protect your presentation content.
How to view Workbook Statistics in Excel
the workbook statistics feature in excel will help us review all the content that you are using in the data table such as using formulas, forms, viewing the number of words in excel, the number of pages or numbers. special characters.
Summary of the most useful Excel keyboard shortcuts you should know
summarize all the shortcuts in excel 2003, 2007, 2010, 2013, 2019. proficiently use excel with extremely useful shortcuts (hotkey). share 100+ excel keyboard shortcuts that you should know
Steps to enable security features on Microsoft 365
microsoft 365 has come a long way when it comes to security. previously rudimentary security systems can now offer features such as automatic scanning of email attachments, protection against phishing and ransomware.
How to crop pictures in OneNote app?
to crop pictures in microsoft onenote web, you first need to sign in to the onenote website from any browser. on the notebook screen, select the note document ...
How to align in Office Word standard paper size
the alignment in standard word paper size will help drafting documents become professional, in accordance with regulations, especially when you draft regulations for companies and agencies. how to align in word follow the steps as follows.