Excel Challenge 30 : Identify Top 3 Sales Performers

What You Will Learn

Five sales agents with revenue. You need to flag the top three as PLATINUM, total their revenue, and count them.

The cutoff is the third-highest revenue. LARGE returns that value, and IF compares each row against it:

=IF(B2 >= LARGE($B$2:$B$6, 3), "PLATINUM", "STANDARD")

LARGE($B$2:$B$6, 3) returns the third-highest revenue. The top three revenues are: Charlie (61,000), Diana (59,000), Alice (52,000). LARGE with position 3 returns 52,000 — that is the cutoff. Any agent with revenue >= 52,000 gets PLATINUM.

Charlie (61,000), Diana (59,000), and Alice (52,000) are PLATINUM. The >= comparison means anyone at or above 52,000 qualifies — if another agent matched the cutoff exactly, they would also be included. Bob (48,000) and Ethan (45,000) are STANDARD. The cutoff works even though there are exactly three agents above it — the formula does not hard-code a count, it uses the data itself to set the boundary.

LARGE sets the cutoff. IF compares each row. SUMIFS and COUNTIF summarize.

The audit formulas:

B9: =SUMIFS(B2:B6, C2:C6, "PLATINUM") — total PLATINUM revenue (172,000).
B10: =COUNTIF(C2:C6,"PLATINUM") — number of PLATINUM agents (3).

Alternative: RANK for Full Leaderboard

If you need each agent's exact position: =RANK(B2,$B$2:$B$6). Returns 1 for Charlie, 2 for Diana, 3 for Alice, etc.

Alternative: TAKE + SORT for Winners List

Create a separate winners table: =TAKE(SORT(A2:B6,2,-1),3). Returns the top 3 rows in a sorted spill range.

The LARGE function requires the range to be locked with dollar signs. Without $B$2:$B$6, filling the formula down would shift the range, causing different rows to check different sets of values. The absolute reference ensures every row evaluates against the same full revenue list.

If multiple agents share the third-highest value (e.g., two people with 52,000), the >= comparison would include all of them — potentially more than 3 winners. Decide whether ties expand the group or if a tie-breaker is needed.

The SUMIFS function uses multiple criteria to sum only matching rows. Here it filters by the PLATINUM label in column C, which means the summary stays synchronized with the classification — if a row changes from STANDARD to PLATINUM, the total updates automatically.