Total only the orders above a threshold (SUMIFS)
The problem
Problem statement
You are the analyst for a regional sales team. The manager does not want the whole month, only the big deals: every order at or above the threshold in E1. The log sits in A2:C11 (Rep, Region, Amount) and the total belongs in the Total cell E2.
The threshold must come from E1, not be typed into the formula — case 2 changes it.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Amount | Threshold | 300 |
| 2 | Ada | East | 120 | Total | |
| 3 | Ben | West | 480 | ||
| 4 | Cleo | East | 300 | ||
| 5 | Dana | North | 90 | ||
| 6 | Eli | East | 750 | ||
| 7 | Fay | South | 260 | ||
| 8 | Gus | West | 310 | ||
| 9 | Hana | North | 55 | ||
| 10 | Ivy | East | 640 | ||
| 11 | Jon | South | 200 |
Notes
SUMIFS concatenates a comparison operator with a cell: ">="&E1. Written as ">=E1" the whole thing is text and matches nothing, which returns 0 rather than an error — the quietest way to be wrong in Excel.
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
- The sum range is C2:C11, and the criteria range is the same column
- Build the criteria as a string: ">="&E1
- Shape: =SUMIFS(?, ?, ?) — criteria are joined with &
Official solutions
Show 2 solutions — try it yourself first
Approach 1: SUMIFS with a joined threshold
=SUMIFS(C2:C11, C2:C11, ">="&E1)
The criteria range and the sum range are both C2:C11; only rows whose amount is at least E1 (300) are added: 300 + 480 + 750 + 310 + 640 = 2480.
Approach 2: SUMIF, which defaults to the criteria range
=SUMIF(C2:C11, ">="&E1)
SUMIF takes the criteria range first and reuses it as the sum range when the third argument is omitted. Same answer, one fewer range to keep in sync.
Function guide
FAQ
How do I use a cell as the SUMIFS threshold?
Join the operator to the reference: =SUMIFS(C2:C11, C2:C11, ">="&E1). The & is what turns the operator and the number into one criteria string.
Why does my SUMIFS return 0 when the numbers are clearly there?
Almost always the criteria: ">=E1" inside quotes is literal text, not a threshold. Either join with &, or write the number itself.