Statistical
RANK IF formula: rank values within groups or categories using COUNTIFS
Build a conditional RANKIFS formula with COUNTIFS to rank employees inside their own department and optionally break ties.
Why Excel has no built-in RANKIFS—and how COUNTIFS builds it
RANK.EQ ranks a score against an entire column, mixing Sales reps and Operations staff into one combined list.
An item's descending rank inside its own group equals (how many items in the SAME group have a strictly GREATER value) + 1: COUNTIFS($A$2:$A$6, A2, $B$2:$B$6, ">" & B2) + 1. Swap ">" for "<" when lower numbers win (race times or defect rates).
| Row | Dept (A) | Sales (B) | Group Rank | Tie-Broken Rank |
|---|---|---|---|---|
| 2 | Sales | 85 | 3 | 3 |
| 3 | Sales | 120 | 1 (tie) | 1 |
| 4 | Ops | 95 | 1 | 1 |
| 5 | Sales | 120 | 1 (tie) | 2 |
| 6 | Ops | 70 | 2 | 2 |
Conditional group rank and tie-breaker formulas
To break ties within the same group sequentially down the sheet, replace + 1 with an expanding COUNTIFS($A$2:A5, A5, $B$2:B5, B5).
=COUNTIFS($A$2:$A$6, A2, $B$2:$B$6, ">" & B2) + 1Tie-broken rank within group
Row 3 (first Sales 120) gets rank 1, and Row 5 (second Sales 120) gets rank 2.
=COUNTIFS($A$2:$A$6, A5, $B$2:$B$6, ">" & B5) + COUNTIFS($A$2:A5, A5, $B$2:B5, B5)Inputs and interpretation
Use numeric, nonblank scores and nonempty group labels. COUNTIFS treats * and ? as wildcards and ~ as an escape character; escape those characters in literal group names before using this version. Equal scores share a competition rank (1, 1, 3), not a dense rank (1, 1, 2). The sequential tie-breaker depends on row order and can change after sorting.
Try the example
Use the original values or change them to test your understanding.