About this exercise
A 300-row dataset on one sheet, 3 answer cells, about 10 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.
How to solve it
- J2
=FILTER(A2:E301,(TRIM(C2:C301)=H2)*(E2:E301>H3),"No orders") - H4
=ROWS(FILTER(A2:A301,(TRIM(C2:C301)=H2)*(E2:E301>H3))) - H5
=SUM(FILTER(E2:E301,(TRIM(C2:C301)=H2)*(E2:E301>H3)))
FILTER keeps the rows of A2:E301 for which the include argument is TRUE. Two conditions are combined by multiplying them: TRUE*TRUE is 1, anything else is 0, so (TRIM(C2:C301)=H2)*(E2:E301>H3) is an AND that works across a whole column at once. TRIM matters because "West " with a trailing space is not equal to "West" — and the first large West order in this log is typed exactly like that — while > keeps the $1,000.00 order out, because it is not over $1,000. The optional third argument replaces the #CALC! error with "No orders" when nothing qualifies, and ROWS and SUM around the same FILTER give the count and the total.
Pitfall 1: text comparisons in Excel ignore case but not spaces; TRIM also collapses double spaces inside a name, while CLEAN removes the non-printing characters that copy-pastes bring along. Pitfall 2: use * for AND and + for OR between array conditions; AND() and OR() collapse the arrays to a single TRUE/FALSE and FILTER then returns everything or nothing. Pitfall 3: ROWS(FILTER(...,"No orders")) returns 1 for an empty result because the fallback text is a 1×1 array — count with a FILTER that has no fallback, or with SUMPRODUCT((TRIM(C2:C301)=H2)*(E2:E301>H3)), which returns 0. Alternatives: SUMPRODUCT handles the count, and SUMPRODUCT((TRIM(C2:C301)=H2)*(E2:E301>H3),E2:E301) the total, in Excel 2019; COUNTIFS and SUMIFS are faster on very large tables but cannot TRIM the criteria range, so they miss the 3 rows typed "West " here — clean the column first. Layout: a spilled list grows and shrinks, so never put anything below or to the right of its anchor; SORT(FILTER(...),5,-1) sorts the result by amount, and CHOOSECOLS(FILTER(...),1,5) returns only the columns you need. Version note: FILTER and SORT need Excel 365 or Excel 2021 (CHOOSECOLS: Excel 365); in Excel 2019 the equivalent is an AutoFilter plus SUBTOTAL, or Advanced Filter for a static copy.
Other ways that work
- =SUMPRODUCT((TRIM(C2:C301)=H2)*(E2:E301>H3)) — the count without FILTER (works in Excel 2019)
- =SUMPRODUCT((TRIM(C2:C301)=H2)*(E2:E301>H3),E2:E301) — the total without FILTER
- =FILTER(A2:E301,(TRIM(C2:C301)=H2)*(E2:E301>H3)) — the same list without the fallback; shows #CALC! when nothing matches
- =SORT(FILTER(A2:E301,(TRIM(C2:C301)=H2)*(E2:E301>H3),"No orders"),5,-1) — largest order first (a different order, so not for this check)
Common mistakes
Comparing the region without TRIM: =FILTER(A2:E301,(C2:C301=H2)*(E2:E301>H3)) skips the 3 matching orders typed "West " — including the very first one, so J2 shows SO-41012 instead of SO-41008 and H4 shows 48 instead of 51.
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.