About this exercise
A 150-row dataset on one sheet, one answer cell, about 4 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.
How to solve it
- H2
=SUMIF(E2:E151,">500")
SUMIF can test the numbers it adds: =SUMIF(E2:E151,">500") looks at every order total and adds the ones greater than 500. The criterion is written as text — the comparison operator and the number inside quotes — and because the condition and the values are the same column, the optional third argument (sum_range) is not needed. The three orders of exactly $500.00 fail the > test and stay out, which matches the approval rule.
Pitfall 1: ">=500" versus ">500" — one character changes the result by $1,500.00 here; read business rules literally ("above", "at least", "more than") before choosing the operator. Pitfall 2: the threshold typed into the criterion is invisible to whoever changes the policy; =SUMIF(E2:E151,">"&J1) with the threshold in J1 (operator in quotes, joined with &) is the maintainable version and the pattern you need for dates. Pitfall 3: amounts stored as text (from a CSV) are ignored by SUMIF — a quick COUNT of the column against the row count reveals them. Alternatives: SUMIFS(E2:E151,E2:E151,">500") is the same with the sum range first; SUMPRODUCT((E2:E151>500)*E2:E151) is the array form; adding a second condition ("<=1000") in SUMIFS gives a band instead of an open range. Companion metrics: COUNTIF(E2:E151,">500") for the number of large orders and the average as SUMIF/COUNTIF or AVERAGEIF(E2:E151,">500").
Other ways that work
- =SUMIFS(E2:E151,E2:E151,">500") — SUMIFS form; sum range first
- =SUMIF(E2:E151,">"&J1) with 500 typed in J1 — threshold kept in a cell
- =SUMPRODUCT((E2:E151>500)*E2:E151) — array form (not accepted here, because this task asks for SUMIF or SUMIFS)
Common mistakes
Writing ">=500" (or "<500") instead of ">500": the 3 orders of exactly $500.00 are added and the total comes out $1,500.00 too high.
How we grade
Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.
Answers computed with Excel 365 rules and checked in our grading engine on November 16, 2026. Author: GridBelt team.