How to Rank by Group in Excel With COUNTIFS

To rank by group in Excel, use COUNTIFS instead of RANK.EQ: count how many rows in the same group have a higher value, then add 1. The formula =COUNTIFS($A$2:$A$9,A2,$C$2:$C$9,">"&C2)+1 gives each rep a rank inside their own region, with ties sharing a rank. This guide is for anyone who needs "top seller per region" or "fastest per team" rankings, and it works in every version from Excel 2007 on.

Why RANK.EQ can't rank by group#

RANK.EQ compares a number against one whole range, and it has no argument for a condition. So =RANK.EQ(C2,$C$2:$C$9) ranks Kofi against every rep, not just the reps in his region.

All examples use this sales table in A1:C9:

Overall, Kofi ranks 6th: five reps sold more. Inside North, he ranks 2nd, behind only Lena. That second number is what most reports need. The guide to the RANK function in Excel covers the overall rank.

How to rank by group with COUNTIFS#

COUNTIFS counts rows that meet every condition you give it. To rank within a group, give it two conditions: the same group, and a higher value. In D2 type:

=COUNTIFS($A$2:$A$9,A2,$C$2:$C$9,">"&C2)+1

Then fill down to D9 (Windows: Ctrl+D; Mac: ⌘D). Here is what each part does:

The & joins the operator ">" to the value in C2, so for Kofi the condition becomes ">5200". Only Lena (6,100) is in North with more, so the count is 1 and the rank is 2. The COUNTIFS function guide explains the range and criteria pairs.

The $ signs lock both ranges, so they stay on rows 2 to 9 as you fill down. A2 and C2 have no $, so they move to A3 and C3 on the next row.

The result: ranks within each region#

After filling down, each region has its own 1, 2, 3:

The rows don't need to be sorted: COUNTIFS checks the whole range each time. Notice that Mei and Lena both sold 6,100 but are ranked separately, because they are in different regions.

Ravi and Sam tie on 4,800 in North, so both get rank 3. There is no rank 4 in North, which matches how RANK.EQ treats ties. The comparison of RANK.EQ and RANK.AVG explains the other way to handle ties.

How to rank smallest first within a group#

For times, costs or error counts, the smallest value should be rank 1. Change the operator from greater than to less than:

=COUNTIFS($A$2:$A$9,A2,$C$2:$C$9,"<"&C2)+1

Now Ravi and Sam (4,800) share rank 1 in North, and Lena (6,100) becomes rank 4.

How to break ties within a group#

To give every row a different rank, add a second COUNTIFS that counts only the rows above the current one with the same group and value:

=COUNTIFS($A$2:$A$9,A2,$C$2:$C$9,">"&C2)+COUNTIFS($A$2:A2,A2,$C$2:C2,C2)

The second part uses an expanding range: $A$2:A2 grows to $A$2:A3, $A$2:A4 and so on as you fill down. It always counts the current row, so it replaces the +1, and it adds one more for each earlier tie. Ravi keeps rank 3 and Sam, who comes later in the list, gets 4. Use this only when a unique rank is truly needed, such as picking one winner, and tell readers how ties were broken.

How to find the top values in each group#

Sometimes you need the value, not the rank: "what are the best and second-best sales in North?" MAXIFS gives the top one in Excel 2019 or later:

=MAXIFS(C2:C9,A2:A9,"North")

The result is 6,100. For the 2nd, 3rd or nth value, combine LARGE with FILTER in Excel 2021 or Microsoft 365:

=LARGE(FILTER(C2:C9,A2:A9="South"),2)

This returns 6,400, the second-highest South sale. In older versions, =LARGE(IF(A2:A9="South",C2:C9),2) does the same, but you must confirm it with Ctrl+Shift+Enter as an array formula. SMALL works the same way for the lowest values.

If you need group averages next to the ranks, the guide to averaging by group uses the same conditions with AVERAGEIFS, and COUNTIFS and SUMIFS with multiple conditions shows how to add a third condition, such as a month.

Common mistakes when ranking by group#

Check these when the ranks look wrong:

• Missing $ signs. Without them, the ranges shift as you fill down and later rows skip part of the data.
• The operator outside the quotes. Write ">"&C2, not >C2 or ">C2". Inside quotes, C2 is just text.
• Forgetting the +1. Without it, the top rep in each group gets rank 0.
• Extra spaces in group names. "North " with a trailing space is a different group from "North". Clean the column with TRIM.
• Numbers stored as text. Text values are not counted as greater or smaller. Convert them with VALUE first.

Practise it#

Ranking by group is a classic job-test question because it combines COUNTIFS, locked ranges and a little logic. Practise COUNTIFS and SUMIFS in a live spreadsheet, then practise LARGE and SMALL for top and bottom values: you type the formula and it checks it instantly. Or try the exercise on this page and rank Kofi within his region.