How to rank values within groups
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Rank each row against only its own category — top salesperson per region, fastest runner per team — instead of one overall ranking.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =COUNTIFS(A2:A100,A2,B2:B100,">"&B2)+1 | Counts how many in the SAME group (col A) score higher (col B), plus 1. No RANKIF exists, so COUNTIFS is the standard trick. Descending; flip > to < for ascending. |
| Google Sheets | =COUNTIFS(A2:A100,A2,B2:B100,">"&B2)+1 | Identical. |
| LibreOffice Calc | =COUNTIFS(A2:A100,A2,B2:B100,">"&B2)+1 | Identical. |
How it works
There's no RANKIF or RANK-by-group function, so the idiom is COUNTIFS: count how many rows share this row's group AND beat its value, then add 1 for the rank. For the first East row (50), the other East values are 80 and 70 — both higher — so two beat it and its rank within East is 3. Each region restarts at 1 automatically because the group condition (A = this row's A) scopes the count. Swap the ">" for "<" to rank ascending (smallest = 1). Ties share a rank and create gaps (two 1sts, no 2nd); to break ties by row order add a running COUNTIFS on the rows above. This drags down cleanly as a helper column and pairs well with a filter to show 'top N per group'.
Verified, not just documented
We ran =COUNTIFS(A2:A5,A2,B2:B5,">"&B2)+1 in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 3 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 3 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
COUNTIFS — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to count cells between two values
- How to count dates within a range
- How to count with multiple criteria (COUNTIFS)
- How to use COUNTIFS (count with multiple conditions)