Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

How to RANK Items in Excel Using the COUNTIFS Function

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. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
I have a spreadsheet that contains a list of names/IDs and I want to generate a formula that inserts into a cell the number of times a name or ID appears on that list. Either insert a column into that spreadsheet or transfer that info into a column on a different spreadsheet. Either way would be fine.
Solved by O. L. in 18 mins
Hello, I have a spreadsheet with 3159 rows. One column consists of 9-digit numbers in each of the 3159 rows. I need the formula that can calculate how many of those numbers appear more than once.
Solved by D. H. in 56 mins
I need a formula to count the number of rows that contain at least one of two possible phrases, but not give me the total number of instances that both phrases occur overall.
Solved by E. B. in 33 mins
how can i turn my sumifs into a countifs because my sumifs works great and i also want to count the thing im summing
Solved by O. C. in 40 mins
im creating a fantasy hockey model and trying to figure out how to rank if someone has played more than a certain number of games, and if they're certain position how to rank them accordingly
Solved by X. D. in 60 mins

Leave a Comment

avatar
juris hazners
Guest
juris hazners

it works fine in this example because points are natural numbers. i need to rank percentages with digits. i have two equal values of 28.06%. and it fails. of course, i can multiply percentages to 100 and round them, but this requires two more steps.

Comment awaiting moderation

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc