Statistical · conditional sum · Nov 2026

The SUMIF function,
measured & explained.

Syntax, a live playground, a measured criteria gallery, the optional sum_range trap, and a sourced error library: add cells that meet one condition.

fx
LookupLab Research
By LookupLab Research·Updated Nov 16, 2026·Engine: HyperFormula 3.5·What SUMIF returns
D1
fx
=SUMIF(A2:A5, "Apple", B2:B5)
0
measured gallery rows
0
sourced errors fixed
0
platforms checked
▶ Try the playground Try SUMIF live ↓See the 8 errors
// 01 — Anatomy

How SUMIF works

Adds cells that meet one criteria. The criteria argument supports operators and wildcards; sum_range is optional and must match range in size.

=SUMIF(range, criteria, [sum_range])
rangeRequired. The cells to test against the criteria.
criteriaRequired. A number, text, operator (">150") or wildcard ("*an*").
sum_rangeOptional. The cells to sum; if omitted, range itself is summed.
// 02 — Conditional sum

What SUMIF actually matches

SUMIF adds cells that meet one criteria. The criteria argument is where the surprises live: operators, wildcards, and the optional sum_range. Every row was measured live in HyperFormula on the sample table. Click any row to load it.

Measured criteria gallery

ExpressionResultWhy
=SUMIF(A2:A5,"Apple",B2:B5)250Sum amounts where item is Apple (100+150)
=SUMIF(B2:B5,">150")5002-arg form: sum the range itself where >150
=SUMIF(B2:B5,">=150")650Inclusive: 150+200+300
=SUMIF(A2:A5,"*an*",B2:B5)200Wildcard *an* matches Banana
=SUMIF(D2:D5,"East",B2:B5)250Sum the East region
=SUMIF(C2:C5,">10",B2:B5)650Criteria on qty, sum amount (cross-column)
=SUMIF(A2:A5,"<>Apple",B2:B5)500Not-equal: everything except Apple
=SUMIF(B2:B5,"<200")250Sum values below 200 (100+150)

Criteria anatomy

The criteria can be a number, text, comparison operator, or wildcard pattern. Measured behavior:

CriteriaMatchesNote
"Apple"exact textCase-insensitive equality
">150"numbers > 150Operator must be inside the quotes
"*an*"contains "an"* = any run of characters
"?pple"5-char "…pple"? = exactly one character
"<>Apple"not Apple<> is the not-equal operator
⚠ The optional sum_range. SUMIF(range, criteria) sums range itself. SUMIF(range, criteria, sum_range) tests range but sums sum_range — they must be the same size or results misalign.
Operator inside the quotes. Write ">150", not >150. To use a cell value, concatenate: ">"&E2.
// 03 — Try it live

Interactive SUMIF playground

Edit the table or type any formula — the engine evaluates instantly. Load a preset to see SUMIF match text, operators and wildcards, and sum a cross-column range.

A1Sample data — click any cell to edit, the formula re-evaluates live
Result
—
✓ readyHyperFormula 3.5
// 04 — Error library

The real-world error library

8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.

🔍
// 05 — Cross-platform

Where SUMIF works

SUMIF is available across modern Excel, Google Sheets and LibreOffice. It has been in Excel since 2003.

PlatformSupportNotesGotcha
✓ Supported full SUMIF◐ Partial works with caveatsCompiled from public docs · verify against your build
// Go deeper

Master conditional math

SUMIF is one piece. The full course walks the toolkit end to end with live, measured examples.

  • SUMIF / COUNTIF — one condition
  • SUMIFS / COUNTIFS — many conditions
  • Criteria: operators, wildcards, case
  • The optional sum_range alignment trap
Free while in beta
Browse the function library

No account needed. Every page has a live playground.

// 07 — FAQ

Frequently asked