How to use the RANK and SUMPRODUCT functions in Excel with COUNTIF
Sort items in ascending or descending order using a combination of the relatively simple COUNTIF, RANK.EQ, and SUMPRODUCT functions. You can sort items with or without duplicates and maintain control to skip or not skip any ranks in the sort sequence.
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.
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.
You should read it
- How to use COUNTIF function on Excel
- How to use SUMPRODUCT function in Excel
- COUNTIF function - Function that counts the number of cells in a data table that meet certain conditions in Excel
- Excel counting functions: Count and Countif
- How to compare data on 2 Excel columns
- The use of the RANK function in excel
- RANK function - Rank function in Excel - Usage and examples
- How to rank on Excel with RANK function
- RANK.AVG function - The function returns the rank of a number in a list of numbers in Excel
- How to use the RANK function in Excel
- PERCENTRANK function - The function returns the rank of the value in Excel
- RANK.EQ function - Function returns the rank of a number in a list of numbers, returns the highest rank when multiple values with the same rank in Excel
Maybe you are interested
How to turn off Update Link notification in Excel - File content from many different sources Create Autotext in Word 2007, edit documents faster Alignment in Word 2010, beautiful alignment of Word 2010 documents How to master numerical data in Google Sheets with the AVERAGE function Excel 2016 - Lesson 8: How to format numbers in Excel (Number Formats) How to change commas to dots in Word - Number unit separator