SUMIF
SUMIF(range, criteria, [sum_range]) adds every cell in sum_range where its matching cell in range meets criteria; omit sum_range and SUMIF sums range itself. Searching for SUMIFS instead? SUMIFS handles multiple conditions and reverses the argument order: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) puts the range being summed FIRST, before any conditions — the opposite of SUMIF, which puts sum_range last and makes it optional. Mixing up that order is the most common SUMIF/SUMIFS mistake, and it rarely throws an error — Excel just sums the wrong range and returns a plausible-looking wrong total. Use SUMIF for exactly one condition; use SUMIFS the moment you need two or more.
The formula
=SUMIF(B2:B11,"Widget",C2:C11) sum C where B matches one condition
=SUMIF(B2:B11,">100") sum_range omitted -> sums B2:B11 itself where B2:B11 > 100
=SUMIF(B2:B11,"<>",C2:C11) sum C wherever B is not blank
=SUMIF(Sheet2!B:B,"Widget",Sheet2!C:C) range and sum_range on another sheet
=SUMIFS(C2:C11,B2:B11,"Widget",D2:D11,">="&DATE(2026,1,1)) SUMIFS: sum_range FIRST, then each range/criteria pairA worked example
B2:B11 lists the category of 10 orders, in row order: Widget, Gadget, Widget, Gadget, Widget, Gadget, Widget, Tool, Tool, Gadget — so rows 2, 4, 6 and 8 are "Widget". C2:C11 holds each order's dollar amount in the same row order: 300, 150, 280, 220, 310, 175, 295, 240, 160, 210 — so the four Widget rows are 300, 280, 310 and 295.
=SUMIF(B2:B11,"Widget",C2:C11)
$1,185 — the sum of the four rows marked "Widget". The match is case-insensitive but needs the whole cell: "widget" in lowercase still counts, but "Widget Kit" or "Widgets" do not, since SUMIF without wildcards requires an exact match, not a partial one.
Which one do I need?
| If you want to… | Use |
|---|---|
| One condition on one range — the ordinary case | =SUMIF(range, criteria, sum_range) |
| Two or more conditions, or you arrived here searching SUMIFS | SUMIF can't do this alone — you need SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...), which puts the summed range first |
| Criteria is a comparison — a date range, or "greater than" a number | =SUMIF(range,">="&DATE(2026,1,1),sum_range) or =SUMIF(range,">100",sum_range) — build the operator into the criteria string |
| range and sum_range live on a different sheet than the formula | =SUMIF(Sheet2!B:B,"Widget",Sheet2!C:C) — qualify both ranges with the sheet name |
| Criteria should match text that contains a word, not the whole cell exactly | =SUMIF(range,"*widget*",sum_range) — wildcards, not a separate function |
| You want a count of matching rows, not a sum | =COUNTIF(range,criteria) — same one-condition shape, no sum_range |
Frequently asked questions
What's the difference between SUMIF and SUMIFS?
SUMIF(range, criteria, [sum_range]) handles exactly one condition, with the summed range last and optional. SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) handles two or more conditions, and reverses the order so the summed range comes first and is required. Reach for SUMIFS the moment a second condition is needed — SUMIF has no way to add one.
Why does my SUMIF return 0 when I can see matching rows?
The usual cause is that criteria doesn't exactly match the cell's full content — a trailing space, or numbers stored as text being compared against a numeric criteria. SUMIF's text match is case-insensitive but requires the whole cell to match unless you add wildcards (*) around the criteria.
Does SUMIF work the same in Google Sheets?
Yes — SUMIF(range, criterion, [sum_range]) and SUMIFS(sum_range, criteria_range1, criterion1, ...) both take identical arguments in the same order in Google Sheets, including the reversed sum_range position on SUMIFS.
Can SUMIF add up two different criteria on the same range?
Not in one call, but you can add two SUMIF calls together for OR logic on a single range: =SUMIF(range,crit1,sum_range)+SUMIF(range,crit2,sum_range). For AND logic across two different ranges — both conditions must be true at once — that's what SUMIFS is for.
New guides and tools, once a month
DE + EN · double opt-in · no spam