SUMIF function
SUMIF adds only the numbers that meet one condition — the workhorse behind every regional total, category subtotal, and 'sum if greater than' question in job interviews. Learn the 3 arguments, the criteria quoting rules, and the SUMIFS order trap.
SUMIF Syntax & The 3 Arguments
SUMIF (conditional sum) checks each cell in range against criteria, then adds the matching numbers from sum_range — filtering and summing in a single formula.
The range to test against criteria (e.g. the region column A2:A7). SUMIF evaluates every cell here.
The condition: text needs quotes ("North"), numbers don't (100), comparison operators are quoted strings (">100"), joinable with cells (">" & D1), wildcards allowed ("North*").
The actual numbers to add (e.g. sales in B2:B7). Omit it and SUMIF sums range itself (e.g. summing B2:B20 where values exceed 100).
Excel SUMIF example
Column A lists regions and column B lists sales. This SUMIF example totals only the North rows.
| Region | Sales |
|---|---|
| North | 1200 |
| South | 800 |
| North | 950 |
| East | 600 |
| North | 400 |
SUMIF checks A2:A6 for North and adds the matching sales in B2:B6: 1200 + 950 + 400 = 2550. Text criteria stay in quotes.
SUMIFS example
The same sheet adds a product column. This SUMIFS example totals North sales of Widgets only — two conditions, and the sum range comes first.
| Region | Product | Sales |
|---|---|---|
| North | Widgets | 1200 |
| South | Widgets | 800 |
| North | Gadgets | 950 |
| North | Widgets | 400 |
SUMIFS adds C2:C5 only where the region is North and the product is Widgets: 1200 + 400 = 1600. Put the sum range first. Reversing it into SUMIF order returns 0 instead of an error.
4 Critical SUMIF Gotchas & Pitfalls
1. SUMIFS reverses the argument order (top interview trap)
SUMIF puts criteria first, sum range last. SUMIFS flips it: =SUMIFS(sum_range, range1, criteria1, …). Reversing the order throws no error — it just returns 0 or wrong numbers, which is exactly why interviewers love this question.
2. Criteria quoting rules
Text criteria must be quoted: "North". Numbers don't need quotes: 100. Comparison operators are a text string: ">=100". Writing >100 bare inside the formula is a syntax error.
3. Case-insensitive matching + wildcards over-count
SUMIF matching ignores case (north = North), and "North*" also hits North America and Northwest. If your data contains 'Norten', use exact values or verify with SUMPRODUCT — don't let wildcards silently over-count.
4. The three culprits behind a 0 result
Hidden spaces around the criteria value (fix with TRIM), numbers stored as text in sum_range (fix with VALUE), or misaligned range/sum_range starting points — none of these throw an error, they all just return 0.
Conditional Sum Showdown: SUMIF vs SUMIFS vs SUMPRODUCT
SUMIF
The standard answer for single-condition sums. Shortest syntax, most readable — the first reflex for 'total by region / by category' interview questions.
SUMIFS
The correct tool for two or more conditions. Mnemonic: sum range FIRST, then condition pairs — the opposite of SUMIF. Not available in pre-2007 legacy workbooks.
SUMPRODUCT
The universal fallback. =SUMPRODUCT((A2:A7="North")*B2:B7) achieves conditional sums via boolean arrays, scales to arbitrarily complex logic, and doubles as the weighted-average formula.
Excel vs Google Sheets Features
Microsoft Excel (365 / Desktop)
Excel's SUMIF accepts cross-sheet references: =SUMIF(Sheet2!A:A, "North", Sheet2!B:B). Note the range argument doesn't take array constants — for multi-condition sums switch to SUMIFS or SUMPRODUCT.
Google Sheets
Google Sheets keeps the syntax identical and adds =ARRAYFORMULA(SUMIF(...)) expansion; =QUERY() can also group-and-sum in one formula, but SUMIF/SUMIFS remains the standard answer in interviews.
Recommended Conditional-Sum Challenges
From single-condition SUMIF to multi-condition SUMIFS and SUMPRODUCT weighted averages — build muscle memory in real business scenarios.
FAQ
How does SUMIF work in Excel?
=SUMIF(range, criteria, sum_range) scans range for cells matching criteria and adds the matching cells from sum_range. Example: =SUMIF(A2:A7, "North", B2:B7) totals the sales of the North region.
Why does my SUMIF return 0?
The usual suspects: 1) text criteria without quotes, 2) numbers stored as text in the sum range, 3) invisible spaces around the criteria value (fix with TRIM), 4) sum_range and range pointing at different-sized areas.
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition; SUMIFS takes two or more. Watch the argument order: SUMIFS puts sum_range FIRST — =SUMIFS(sum_range, range1, criteria1, range2, criteria2) — the exact reverse of SUMIF, and the #1 trap in interview tests.
How do I sum cells greater than a value?
Wrap the operator in quotes: =SUMIF(B2:B20, ">100") adds only the cells above 100. For a threshold stored in a cell, join it: =SUMIF(B2:B20, ">" & D1).
Can I use wildcards in SUMIF?
Yes — * matches any number of characters and ? matches exactly one, so =SUMIF(A2:A20, "North*") sums every region starting with 'North'. Matching is case-insensitive on both Excel and Google Sheets.