Statistical
RANK IF formula: rank values conditionally within groups or months (COUNTIFS)
Build a native RANKIF equivalent using COUNTIFS($A$2:$A$7, A2, $B$2:$B$7, ">" & B2) + 1 to rank reps within regions, products within categories, or sales within months.
Spreadsheets have SUMIF, COUNTIF, AVERAGEIF, MAXIFS—so how do you write RANKIF?
A descending competition rank is one plus the number of values strictly greater than the current value within its group. Equal values share a rank and leave gaps, such as 1, 1, 3; this is not a unique ordering or a dense rank.
This example requires numeric values in all six data rows and nonempty text group labels. Zero and negative values are valid. Blank values and numbers stored as text are rejected rather than silently ranked. The grouping comparison is case-insensitive.
COUNTIFS criteria interpret wildcard characters. Escape tilde first, then asterisk and question mark, so a group label such as West* is treated literally. Prefix the escaped criterion with = to avoid interpreting leading comparison signs. For monthly groups, use a consistent year-month key so the same month in different years is not accidentally combined.
| Region (A) | Revenue (B) | Higher Peers in Same Region | Regional RANK IF |
|---|---|---|---|
| West | $420 | 2 higher in West ($680, $550) | 2 + 1 = Rank 3 (West) |
| East | $510 | 1 higher in East ($720) | 1 + 1 = Rank 2 (East) |
| West | $680 | 0 higher in West | 0 + 1 = Rank 1 (West) |
| East | $720 | 0 higher in East | 0 + 1 = Rank 1 (East) |
Descending RANK IF within group (1 = highest value in group)
Counts rows sharing group A2 whose value exceeds B2, then adds 1 (ties receive identical standard competition ranks 1, 1, 3).
=IF(OR(COUNT($B$2:$B$7)<>6,NOT(ISTEXT(A2)),COUNTBLANK(A2)>0,NOT(ISTEXT(A3)),COUNTBLANK(A3)>0,NOT(ISTEXT(A4)),COUNTBLANK(A4)>0,NOT(ISTEXT(A5)),COUNTBLANK(A5)>0,NOT(ISTEXT(A6)),COUNTBLANK(A6)>0,NOT(ISTEXT(A7)),COUNTBLANK(A7)>0),"Invalid inputs",COUNTIFS($A$2:$A$7, "=" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"~","~~"),"*","~*"),"?","~?"), $B$2:$B$7, ">" & B2) + 1)Ascending RANK IF within group (1 = smallest / fastest in group)
Flipping ">" to "<" ranks lowest costs, Golf scores, or fastest lap times #1 inside each group.
=IF(OR(COUNT($B$2:$B$7)<>6,NOT(ISTEXT(A2)),COUNTBLANK(A2)>0,NOT(ISTEXT(A3)),COUNTBLANK(A3)>0,NOT(ISTEXT(A4)),COUNTBLANK(A4)>0,NOT(ISTEXT(A5)),COUNTBLANK(A5)>0,NOT(ISTEXT(A6)),COUNTBLANK(A6)>0,NOT(ISTEXT(A7)),COUNTBLANK(A7)>0),"Invalid inputs",COUNTIFS($A$2:$A$7, "=" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"~","~~"),"*","~*"),"?","~?"), $B$2:$B$7, "<" & B2) + 1)Try the example
Use the original values or change them to test your understanding.