Working with data in Excel often requires you to rank data. Excel has a built-in RANK function for this purpose. But the RANK function has some limitations. One of them is RANK fails to rank data uniquely when there is duplication. You can use a neat little trick to overcome this shortcoming.
Using two COUNTIF functions together help us to rank data even if there is duplication. The COUNTIF function can also be used to rank data based on multiple criteria. In this tutorial, you will learn how to rank data using the COUNTIF function.
How to Rank in Excel rank using the COUNTIF function
You will use a high school soccer league point table data set in the next example. There are two pairs of duplicated values in this example. Using the RANK function in column C, we get the same rank for duplicated values.
To fix this issue:
- Go to cell D2 and select it with your mouse.
- Apply the formula to
=COUNTIF($B$2:$B$8,">"&$B2)+COUNTIF($B$2:B2,B2)cell D2. - Press Enter.
- Drag the formula to the cells below.
This will rank all the values uniquely in column C with no repetitions in descending order. The first COUNTIF function is used to find out the number of values greater or less than the number that is going to be ranked. The second COUNTIF has an expanding range $B$2:B2 and extracts the number of values that are equal to the number.
Rank in Excel Using Multiple Criteria
In the previous example, Blazone Warriors are ranked lower than Bento All Stars despite having a higher goal difference. You can use the RANK.EQ with COUNTIFS to add multiple criteria to have an improved ranking using criteria. For this example:
To fix this issue:
- Go to cell D2 and select it with your mouse.
- Apply the formula
=RANK.EQ($B2,$B$2:$B$8)+COUNTIFS($B$2:$B$8,$B2,$C$2:$C$8,">"&$C2)to cell D2. - Press Enter.
- Drag the formula to the cells below.
This will show Blazone Warriors ahead of Bento All Stars in the rankings. The first formula with the criteria ($B2,$B$2:$B$8) counts the number of times the values appear. The formula performs a row-wise check for the value as you have an absolute reference but a relative one for the rows ($B2).
The second formula with the criteria ($C$2:$C$8,”>”&$C2) checks how many goal differences are greater than the one being checked. This returns 0 for Blazone Warriors. However, Bento All Stars has 1 returned as the other team has a higher goal difference.
Rank in an Ascending Order using the COUNTIF function
You can rank in an ascending order using the COUNTIF function. You will use the fruit sales data in this example. To rank the sales in an ascending order:
- Go to cell C2 and select it.
- Apply the formula to
=COUNTIF($B$2:$B$7,"<"&$B2)+COUNTIF($B$2:B2,B2)C2. - Press Enter.
- Drag the formula with your mouse to the cells below.
This will rank the sales in an ascending order.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.
Leave a Comment