The Problem
Top-performer lists are easy to describe and easy to get wrong by hand. Once several agents are involved, scanning the numbers and deciding who belongs in the top three becomes a lot less reliable than it sounds.
This challenge makes the ranking rule explicit. We need to flag the top three earners inside the table, then calculate the combined revenue from that winning group and count how many winners were found.
How We Solve It
The cleanest shortcut is to find the third-largest revenue and use it as the entry bar. Anyone whose revenue is greater than or equal to that value belongs in the top group. After that, the summary cells become straightforward.
Method 1: Use LARGE as the cutoff
Method 1: Use the third-highest value as the cutoff for the top group.
This is the method used in the solution. LARGE($B$2:$B$6, 3) returns the third-highest revenue. Once we have that benchmark, IF can mark each row as PLATINUM or STANDARD.
This solves the challenge because the worksheet needs one clear cutoff that defines who belongs in the top three. Once the third-highest revenue is known, every row can be tested against that single benchmark without sorting the table by hand.
=IF(B2 >= LARGE($B$2:$B$6, 3), "PLATINUM", "STANDARD")
Method 2: Rank every agent
Method 2: Assign each agent a position in the leaderboard.
RANK is useful when you want to see each agent's exact position instead of just whether they made the cut. It is not the formula pattern used by the validator, but it is a natural companion to the same top-three logic.
This solves a more detailed version of the leaderboard problem. Instead of only telling us whether someone made the top group, it shows each agent's standing, which is useful when the report needs full ranking information.
=RANK(B2, $B$2:$B$6)
Method 3: Build a separate winners list
Method 3: Create a separate winners list with sorting functions.
Modern Excel can also sort the table and return only the top rows. That is helpful when a report needs a separate winners section instead of labels inside the main table.
This solves the reporting problem when the business wants a standalone winners list. After sorting the table by revenue, the formula can return only the top performers without changing the original data block.
=TAKE(SORT(A2:B6, 2, -1), 3)
Function Explanation
1. LARGE
LARGE returns the nth largest value in a range. In this challenge it provides the exact revenue threshold that defines who belongs in the top three.
Learn more this functionLARGE
2. IF
IF turns the threshold comparison into a visible category label. That is what makes the ranking easy to read in the main table.
Learn more this functionIF
3. SUMIFS
SUMIFS adds only the revenue rows that match the PLATINUM label. That gives the summary total for the winners without extra helper columns.
Learn more this functionSUMIFS
Ties are the one thing to think about in real leaderboards. If several people share the third-highest value, a cutoff formula can include more than three winners, which may or may not be what the business wants.
Flag the top three earners in column C, then complete the summary so the sheet shows the total revenue from the winners and the number of agents who made that group.