The Excel SUMIF function, explained interactively.
SUMIF walks through a range, tests each cell against a criterion, and adds up the matching values (either in that range or in a parallel one). Change the filter below — watch the total.
How SUMIF works
Change the filter, watch the total. This is a SUMIF on a tiny expense sheet — pick a category and the formula adds up only the rows that match.
A2:A7."Food", a comparison like ">50", or a wildcard like "F*".| A | B | |
|---|---|---|
| 1 | Category | Amount |
| 2 | Food | 12.40 |
| 3 | Travel | 88.00 |
| 4 | Food | 6.75 |
| 5 | Office | 21.30 |
| 6 | Travel | 42.00 |
| 7 | Food | 9.10 |
| 8 | ||
| 9 | Total (Food) | 28.25 |
SUMIF syntax and arguments
Three arguments, in order. The third is optional — and skipping it only makes sense when the range itself holds the numbers you want to sum.
criteria, e.g. A2:A100. Every cell is checked one by one. This is not necessarily the column you sum — that’s sum_range. When SUMIF has no sum_range, it sums range itself, which only makes sense when the range contains numbers."Fruit"), comparison (">10", "<>0"), or wildcard ("*berry"). The operator must be inside the quotes; bare >10 is a syntax error. Build dynamic criteria by concatenating a cell: ">"&B1.range. When omitted, SUMIF sums range itself. Should be the same shape as range; non-numeric cells are counted as 0.SUMIF examples
Three patterns you’ll reach for again and again.
Example 1: SUMIF to total by category
The canonical SUMIF: given a ledger, total just the rows whose category matches.
Walks down column A, finds every “Fruit” row, sums the corresponding Amount from column C.
Example 2: SUMIF with a numeric comparison
No sum_range needed — the range itself contains the numbers we want to filter and sum.
Sums every value in C2:C100 that is at least 50. Omitting the third argument is an intentional shortcut here.
Example 3: SUMIF with a cell reference
Build the criterion from a cell reference so the user of your sheet can pick a category.
If E1 holds “Dairy”, this totals every Dairy row. Change E1, watch the total update — ideal for dashboards.
Common SUMIF errors and fixes
Four failure modes, each with what to check and how to recover.
SUMIF returns 0 unexpectedly
Cause: the criterion matches no rows, or sum_range points at text.
Check for invisible whitespace (TRIM), number-stored-as-text in sum_range, or a wrong criterion like "10" vs 10 in a numeric column.
#VALUE! or #NAME? in a SUMIF
Cause: operator outside the quotes, or a typo in the function name.
Write ">10", not >10. Concatenate with a cell ref using ">"&B1.
SUMIF wildcards aren’t matching
Cause: wildcards only apply to text cells with exact-style criteria.
Use "*" and "?" inside the criterion string. For a literal * or ?, prefix with ~.
SUMIF: mismatched range and sum_range sizes
Cause: range and sum_range must line up row-for-row.
Modern Excel forgives mismatched size by reshaping from the top-left, but you should write them with identical dimensions for clarity.
SUMIF vs SUMIFS, COUNTIF & AVERAGEIF
The same filter-then-aggregate idea applied to sums, counts, and averages — with one crucial argument-order twist.
| Function | Criteria | Aggregates | Argument order |
|---|---|---|---|
| SUMIF | One | Sum | range, criteria, [sum_range] |
| SUMIFS | One or many | Sum | sum_range, range1, crit1, range2, crit2, … |
| COUNTIF | One | Count | range, criteria |
| AVERAGEIF | One | Average | range, criteria, [average_range] |
Watch the argument order when switching from SUMIF to SUMIFS: sum_range comes first in SUMIFS. This is the single most common reason people mis-convert formulas.
SUMIF frequently asked questions
6.01Can SUMIF handle multiple criteria?▸
No. SUMIF supports exactly one condition. For two or more, use SUMIFS (note the trailing S) — its argument order starts with sum_range.
6.02Is SUMIF case-sensitive?▸
No. “fruit”, “Fruit”, and “FRUIT” all match. For case-sensitive summing, use SUMPRODUCT with EXACT.
6.03How do I use a cell reference in SUMIF criteria?▸
A bare cell reference works on its own for equality: =SUMIF(A2:A100, E1, C2:C100) totals every row whose category equals whatever is in E1. For operators, concatenate with &: ">"&B1 builds greater than whatever is in B1 — the operator has to stay inside quotes.
6.04Why does my SUMIF ignore obvious numbers?▸
Those numbers are probably stored as text. Select the column and use Convert to Number, or multiply a helper column by 1. SUMIF won’t coerce text numbers automatically in sum_range.
6.05When should I use SUMIFS instead of SUMIF?▸
Use SUMIFS the moment you need a second condition — SUMIF supports only one. Remember SUMIFS puts sum_range first in its argument list, which is the single most common source of mis-converted formulas. For OR logic or regex-like matching, SUMPRODUCT or modern FILTER + SUM are often cleaner than either.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.