Rank salespeople within each region, or students within each class — a separate leaderboard per group. COUNTIFS counts how many in the same group score higher, giving a per-group rank.
The example
Ranked inside each region.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Sales | Rank |
| 2 | East | 95 | 1 |
| 3 | East | 80 | 2 |
| 4 | West | 90 | 1 |
The formula
The formula:
How it works
How it works:
COUNTIFS(groups, A2, values, ">"&B2)counts how many rows in the same group have a higher value.- Add 1 so the top value is rank 1.
- This gives an independent ranking within each group, unlike RANK which ranks across everything.
- Use
<instead of>to rank ascending (smallest = rank 1).
Ties share a rank. Two equal values both get the same rank, and the next value skips one (1, 1, 3). To break ties, add a tiebreaker COUNTIFS on a second column, or use the row order.
Try it: interactive demo
Lines “group,value”; rank within group.
Variations
Ascending rank
Smallest first:
Break ties
Add a second key:
Top-N flag per group
Mark the top 3:
Pitfalls & errors
Ties share ranks. Equal values get the same rank; add a tiebreaker if you need unique ranks.
Concatenate the criteria. Use ">"&B2, not ">B2".
Aligned ranges. The group and value ranges must cover the same rows.
Practice workbook
Frequently asked questions
How do I rank values within each group in Excel?
How do I rank ascending within a group?
How do I break ties?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class