Top three deals total (SUMIF + LARGE)
The problem
Problem statement
The deal register in A2:C9 lists eight closed orders at very different sizes. The bonus pool is paid on the three biggest deals only, so the register has to answer 'top three combined' without a helper column and without sorting anything. Put that total in the Top 3 total cell E2.
The cut-off is not a fixed number: test cases reshuffle which deals land in the top three, so the boundary must be computed from the data itself.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | E | |
|---|---|---|---|---|
| 1 | Order | Rep | Amount | Top 3 total |
| 2 | ORD-2001 | Ana Li | 1250 | |
| 3 | ORD-2002 | Ben Kim | 800 | |
| 4 | ORD-2003 | Chen Wu | 960 | |
| 5 | ORD-2004 | Dana Ortiz | 600 | |
| 6 | ORD-2005 | Eve Novak | 330 | |
| 7 | ORD-2006 | Ana Li | 700 | |
| 8 | ORD-2007 | Chen Wu | 480 | |
| 9 | ORD-2008 | Ben Kim | 1200 |
Notes
LARGE(range, 4) is the fourth-largest amount, so SUMIF(range, ">"&LARGE(range, 4)) keeps exactly the values above it — the top three. The widely taught sibling is ">="&LARGE(range, 3) which includes the third value directly. Both rely on strict ordering: if two deals tie at the boundary, the strict version under-counts and the ">=" version over-counts, and the tie-safe answer becomes SUM(LARGE(range, {1,2,3})).
How to approach
Your answer goes in E2. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is E2
- Amounts are in C2:C9 — there is no rank column to filter on
- LARGE(C2:C9, 4) returns the fourth-largest amount; anything strictly above it is a top-three deal
- Shape: =SUMIF(?, ">"&LARGE(?, ?), ?)
Official solutions
Show 2 solutions — try it yourself first
Approach 1: strict greater than the fourth-largest
=SUMIF(C2:C9, ">"&LARGE(C2:C9, 4))
LARGE(C2:C9, 4) computes the boundary at run time — the 4th biggest amount — and the criterion adds everything above it. The lavishly named 'top N' pattern is just this with 4 replaced by N+1, so the formula never needs a sorted sheet or a helper column.
Approach 2: greater-or-equal to the third-largest
=SUMIF(C2:C9, ">="&LARGE(C2:C9, 3), C2:C9)
Same three rows, different route: take the 3rd biggest amount as the cut-off and include it. Shorter to reason about, but it quietly breaks when two deals tie at third place — you get four rows instead of three.
Function guide
FAQ
How do I sum the top three values without sorting the sheet?
=SUMIF(C2:C9, ">"&LARGE(C2:C9, 4)) totals everything above the 4th biggest value. For the top N, replace 4 with N+1 — the boundary is computed from the data, so the sheet can stay in any order.
What if two deals tie for third place?
Both variants above become wrong: the strict '>' version silently drops the tied pair (you get fewer than three deals) while '>=' quietly takes four. When ties are possible, use =SUM(LARGE(C2:C9, {1,2,3})), which counts positions, not thresholds.
Can I do this with SUMPRODUCT instead?
Yes: =SUMPRODUCT(LARGE(C2:C9, {1,2,3})) or =SUMPRODUCT((C2:C9>=LARGE(C2:C9,3))*C2:C9). SUMIF is shorter to read; SUMPRODUCT is the fallback when you also need OR logic or a case-sensitive match in the same formula.