The formula
Rank within region (highest = 1) (fill down)ExcelGoogle Sheets
=COUNTIFS($B$2:$B$9,B2,$C$2:$C$9,">"&C2)+1In E2 → 3
Overall rank (fill down)ExcelGoogle Sheets
=RANK.EQ(C2,$C$2:$C$9)In F2 → 5
✓ Tested. All 16 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Region | Score | |||
| 2 | Ana | East | 72 | 3 | 5 | |
| 3 | Ben | West | 88 | 2 | 3 | |
| 4 | Cara | East | 95 | 1 | 2 | |
| 5 | Dev | West | 61 | 3 | 6 | |
| 6 | Eli | East | 50 | 4 | 7 | |
| 7 | Fay | West | 100 | 1 | 1 | |
| 8 | Gus | East | 83 | 2 | 4 | |
| 9 | Hal | West | 47 | 4 | 8 |
How it works
- $B$2:$B$9,B2
- Only rows in the same region.
- $C$2:$C$9,">"&C2
- Count scores higher than this one.
- +1
- The top score has 0 higher, so rank 1.
- Lowest = 1
- Use "<"&C2 instead.
Common errors and fixes
Ties get the same rank?
Expected (like RANK.EQ). Break ties by adding COUNTIFS($B$2:B2,B2,$C$2:C2,C2)-1.
All ranks are 1?
The ranges aren't locked with $.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.