How to rank within groups in Excel
Formula (Excel and Google Sheets)
=COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,">"&C2)+1Count the larger values in the same group and add 1: =COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,">"&C2)+1.
- Result on the example
- one result per row in
D2, filled down toD9 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in D2, filled down to D9. Change any input and the result updates, or edit the formula to experiment.
How it works
A rank is one more than the number of values above you. COUNTIFS counts reps in the same region (B2) with higher sales (">"&C2): Ana has two East reps above her, so she is 3rd in the East. Cara and Hana tie with nobody above them and share 1st, like RANK.EQ.
=COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,">"&C2)+1Result3
$B$2:$B$98 rows × 1 col: "East", "West", "East", "East", …A range: 8 cells in one column.
B2"East"The value in B2.
$C$2:$C$98 rows × 1 col: $48,200, $61,750, $57,300, $39,800, …A range: 8 cells in one column.
C2$48,200The value in C2.
">"&C2">48200"The & operator joins as text.
COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,">"&C2)2COUNTIFS: Counts rows that meet every condition.- criteria_range1
$B$2:$B$98 rows × 1 col: "East", "West", "East", "East", … - criteria1
B2"East" - criteria_range2
$C$2:$C$98 rows × 1 col: $48,200, $61,750, $57,300, $39,800, … - criteria2
">"&C2">48200"
- criteria_range1
COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,">"&C2)+13The + operator adds.
In Google Sheets
The same formula works in Google Sheets: =COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,">"&C2)+1. We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(($B$2:$B$9=B2)*($C$2:$C$9>C2))+1 | 3, 2, 1… | The same count written as arrays; handy when the group test needs a calculation. |
Common mistakes
- Without
$on the ranges, filling down shrinks them and the ranks drift. - RANK.EQ has no group argument, and it needs a cell range, so it can't rank inside a filtered list.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Rank within groups
D2, rank the rep by sales within their own region (highest = 1), then fill down to D9.Questions
How do I rank smallest first within groups?
Count smaller values instead: "<"&C2.
How do I break ties within a group?
Add the ties above the current row: +COUNTIFS($B$2:B2,B2,$C$2:C2,C2)-1.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.