Expense claims over the approval limit (SUMIF)
The problem
Problem statement
The finance export in A2:C8 lists every expense claim, big and small. Only the claims above the approval limit matter for the audit — and the limit itself sits in D2 because it moves every quarter. Put the total of the claims strictly above that limit in the Total above limit cell E2.
Build the criterion from D2 — the limit changes between test cases, so neither 500 nor the answer may be typed into the formula.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Claim | Requester | Amount | Limit | Total above limit |
| 2 | EXP-101 | Ana Li | 420 | 500 | |
| 3 | EXP-102 | Ben Kim | 500 | ||
| 4 | EXP-103 | Chen Wu | 1250 | ||
| 5 | EXP-104 | Dana Ortiz | 80 | ||
| 6 | EXP-105 | Ana Li | 900 | ||
| 7 | EXP-106 | Eve Novak | 610 | ||
| 8 | EXP-107 | Ana Li | 1500 |
Notes
SUMIF(range, criteria, [sum_range]) compares each cell of range against criteria and adds the matching entries of sum_range. When range and sum_range are the same column ('add only the numbers above X'), the third argument can be omitted and SUMIF adds the cells it just tested. The operator has to be joined to the cell with &, because ">"&D2 builds one string at run time.
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 approval limit lives in D2 — the criterion must be built as ">"&D2, never as ">500"
- Amounts are in C2:C8, and the third argument can be omitted because the criteria range is the column you are summing
- Shape: =SUMIF(?, ">"&?, ?)
Official solutions
Show 2 solutions — try it yourself first
Approach 1: SUMIF with the sum range omitted
=SUMIF(C2:C8, ">"&D2)
SUMIF tests C2:C8 against the string built by ">"&D2 and adds the cells that pass. Because no sum range is given, it adds the very cells it tested — the exact shape of 'total only the values above X'.
Approach 2: same formula with the sum range spelled out
=SUMIF(C2:C8, ">"&D2, C2:C8)
Identical result, written explicitly: the criteria range and the sum range are the same column. Useful when you later split the tested column and the summed column apart — the formula keeps working.
Function guide
FAQ
How do I sum only the cells above a threshold?
=SUMIF(C2:C8, ">500") adds only the values above 500. When the threshold is stored in a cell, join the operator to the reference with & — =SUMIF(C2:C8, ">"&D2) — so the formula keeps working when the limit changes.
Why is the total 0 when the limit equals the biggest claim?
Because > is strictly greater. If the largest claim is exactly the limit, no row qualifies and SUMIF returns 0 instead of an error. Use ">=" if a claim equal to the limit should be included.
Is the third argument of SUMIF optional?
Yes. If you omit it, SUMIF adds the cells in the criteria range itself. That is exactly what you want for 'sum only the numbers above X' where the tested column and the summed column are the same.