How to Use SUMIF in Google Sheets (2026 Guide)
SUMIF adds up the cells that meet one condition. The syntax is =SUMIF(range, criterion, sum_range). When you have more than one condition, use SUMIFS. Note the argument order flips between the two, which is the single most common mistake. Here are working examples plus the errors to watch for.
1. SUMIF Syntax
=SUMIF(range, criterion, sum_range)- range: the cells to test against your condition, like
A2:A100. - criterion: the condition, like
"East"or">100". - sum_range: the cells to actually add up, like
B2:B100. Optional, if you leave it out, SUMIF adds the cells inrangeitself.
Total sales for the East region:
=SUMIF(A2:A100, "East", B2:B100)This checks column A for “East” and sums the matching amounts in column B. SUMIF text matching is not case-sensitive.
2. Sum by Number Condition
With a single range and no separate sum_range, SUMIF totals the cells that match:
=SUMIF(B2:B100, ">100") adds every value over 100
=SUMIF(B2:B100, ">=100") 100 or more
=SUMIF(B2:B100, "<50") under 50
=SUMIF(B2:B100, "<>0") everything except zerosTo test one column but sum another:
=SUMIF(B2:B100, ">100", C2:C100)This finds rows where column B is over 100 and adds the matching values in column C.
Compare against a value in a cell by joining with &:
=SUMIF(B2:B100, ">"&E1, C2:C100)3. Sum by Text and Wildcards
Match exact text:
=SUMIF(A2:A100, "Paid", B2:B100)Match text in another cell (no quotes on the reference):
=SUMIF(A2:A100, E1, B2:B100)Use wildcards for partial matches, * for any characters, ? for a single character:
=SUMIF(A2:A100, "*north*", B2:B100) label contains "north"
=SUMIF(A2:A100, "INV-*", B2:B100) label starts with "INV-"Exclude a value with <>:
=SUMIF(A2:A100, "<>Refunded", B2:B100)4. Sum by Date
Dates are numbers, so the operators work directly:
=SUMIF(D2:D100, ">1/1/2026", B2:B100) amounts dated after Jan 1, 2026
=SUMIF(D2:D100, "<="&TODAY(), B2:B100) amounts dated up to today
=SUMIF(D2:D100, DATE(2026,6,29), B2:B100) amounts on a specific dateFor a date range (between two dates), use SUMIFS, covered next.
5. Multiple Criteria with SUMIFS
SUMIFS handles several conditions at once, and all must be true (AND logic). Important: the sum_range comes first in SUMIFS, the opposite of SUMIF.
=SUMIFS(sum_range, range1, criterion1, range2, criterion2, ...)Total East-region sales over 100:
=SUMIFS(C2:C100, A2:A100, "East", B2:B100, ">100")Total Q1 2026 revenue (a date range):
=SUMIFS(C2:C100, D2:D100, ">="&DATE(2026,1,1), D2:D100, "<="&DATE(2026,3,31))Every range in a SUMIFS must be the same size, or you get a #VALUE! error.
Common Errors
- Argument order mixed up: SUMIF is
(range, criterion, sum_range)but SUMIFS is(sum_range, range, criterion). Switching tools means switching order. #VALUE!: in SUMIFS, the ranges are different sizes. Make them match exactly.- Returns 0: the criterion text does not match, often a trailing space, or you compared text to a number stored as text. Check the source cells.
- Operator not working: it must be inside quotes,
">100", not>100. - Cell reference in criterion fails: join with
&, as in">"&E1, never">E1".
Quick Reference
| Goal | Formula |
|---|---|
| Total by text | =SUMIF(A:A, "East", B:B) |
| Total over a number | =SUMIF(B:B, ">100") |
| Test one column, sum another | =SUMIF(B:B, ">100", C:C) |
| Contains word | =SUMIF(A:A, "*north*", B:B) |
| Two conditions | =SUMIFS(C:C, A:A, "East", B:B, ">100") |
| Date range | =SUMIFS(C:C, D:D, ">="&DATE(2026,1,1), D:D, "<="&DATE(2026,3,31)) |
Let Carly Run the Totals You Repeat
SUMIF totals a category in one cell, but rebuilding the same summary every week is recurring work you can hand off. Carly is an AI assistant that connects to 200+ apps including Google Sheets and handles the follow-through, logging transactions into the sheet your SUMIF reads, pulling the resulting totals into an email or task, and sending that recap on a schedule. Carly starts at $35/month.
More on Google Sheets: How to use COUNTIF in Google Sheets · How to use VLOOKUP in Google Sheets · How to use the IF function in Google Sheets · Google Sheets integration
Ready to automate your busywork?
Carly schedules, researches, and briefs you—so you can focus on what matters.
See what people say
"Before Carly, I relied on a Calendly link, but the whole process felt impersonal and not very professional. Carly changed that by handling all the back-and-forth, so I'm no longer stuck in endless email threads trying to line up schedules.
Now Carly reaches out to candidates, shares my real-time availability, lets them pick a slot, then sends a Zoom link and drops it straight into my calendar. She sends reminders to both of us before each call, which has significantly reduced no-shows and last-minute confusion.
On top of scheduling, Carly acts like a full executive assistant, sending me my schedule the night before so I can prepare for each call. It reminds me of the old x.ai assistant, but Carly is noticeably smarter, faster, and better suited to my healthcare recruitment business."
