📎 This article includes 1 downloadable practice file ↓

Ranking looks simple until two salespeople have the same total, or the manager wants “top 3 in each region”. RANK.EQ alone gives duplicates and gaps, and it ranks against the whole list. This post covers the combinations that solve ties and groups.

In this article

1. The basic rank
2. Tie-break to get unique ranks
3. Dense rank (no gaps)
4. Rank within each group
5. Top 3 per region, pulled out
6. Where people go wrong
7. Practice

The basic rank

=RANK.EQ(I2, $I$2:$I$201)        ' largest = 1
=RANK.EQ(I2, $I$2:$I$201, 1)     ' smallest = 1

Two equal amounts both get, say, rank 4, and the next one gets 6 — rank 5 is skipped. That’s “competition ranking”, the same as sports tables.

Tie-break to get unique ranks

=RANK.EQ(I2, $I$2:$I$201) + COUNTIF($I$2:I2, I2) - 1

The COUNTIF counts how many times this amount has appeared so far. The first of a tie keeps its rank; the second gets +1. Every row now has a different rank — useful when you need to pick exactly the top 10 with INDEX/MATCH.

Dense rank (no gaps)

For “1, 2, 2, 3” instead of “1, 2, 2, 4”, count how many distinct values are bigger:

=SUMPRODUCT(($I$2:$I$201>I2)/COUNTIF($I$2:$I$201,$I$2:$I$201))+1

In Excel 365 a clearer version is =XMATCH(I2, SORT(UNIQUE($I$2:$I$201), , -1)).

Rank within each group

Rank each order against others in the same region:

=COUNTIFS($B$2:$B$201, B2, $I$2:$I$201, ">" & I2) + 1

“How many orders in my region are bigger than me? Add one.” Swap the region column for a month or rep column to rank within those instead. Add the tie-break trick if needed:

=COUNTIFS($B$2:$B$201, B2, $I$2:$I$201, ">" & I2) + COUNTIFS($B$2:B2, B2, $I$2:I2, I2)

Top 3 per region, pulled out

Once each row has a group rank in column Q, filtering is easy:

=FILTER(A2:I201, Q2:Q201 <= 3)                         ' 365: all top-3 rows
=SORTBY(FILTER(A2:I201, Q2:Q201<=3), FILTER(B2:B201,Q2:Q201<=3), 1, FILTER(Q2:Q201,Q2:Q201<=3), 1)   ' sorted by region, then rank

Where people go wrong

Problem	Cause	Fix
Ranks change when copied down	Range not locked	$I$2:$I$201
Text numbers ranked wrongly	“5000” stored as text is ignored	Convert to numbers (VALUE or Text to Columns)
Blank rows ranked last / errors	Empty cells in the range	Wrap: =IF(I2="","",RANK.EQ(…))
Group rank counts other groups	COUNTIFS criteria order mixed up	Pair each range with its own criterion

Practice

The combo practice workbook below has a Sales sheet of 200 orders and a task for every formula on this page. Type your formula in the yellow column; the check turns green when the answer matches. The Answers sheet has working versions.

More combinations: all formula combos · functions used here are explained in the Excel function course.

📎 Practice files for this article

• 📗
  Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.
  ⬇ XLSX · 39 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.

Keep reading

Formula CombosGST Calculation in Excel: Add GST, Remove GST, Split CGST/SGST/IGSTFormula CombosReturn ALL Matches in One Cell: TEXTJOIN + FILTER (the VLOOKUP Upgrade)Formula CombosXLOOKUP with Two (or More) Criteria — Plus the INDEX-MATCH Version