# How to rank within groups in Excel

Count the larger values in the same group and add 1: `=COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,">"&C2)+1`.

**Excel:** `=COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,">"&C2)+1` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Rep | Region | Sales | Rank in region |
| Ana | East | 48200 |  |
| Ben | West | 61750 |  |
| Cara | East | 57300 |  |
| Dev | East | 39800 |  |
| Eli | West | 66400 |  |
| Fay | East | 44150 |  |
| Gus | West | 52600 |  |
| Hana | East | 57300 |  |

Formula in D2, filled to D9: `=COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,">"&C2)+1`

Result: 3; 2; 1; 5; 1; 4; 3; 1

## 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.

## Other ways

- `=SUMPRODUCT(($B$2:$B$9=B2)*($C$2:$C$9>C2))+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.

## 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`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/rank-within-groups