SUMIFS with multiple criteria: date ranges, another sheet, wildcards, OR — and why it returns 0

SUMIFS is the function every template runs on: a budget's actuals, an invoice register's outstanding balance, a leave tracker's days per person. It's simple once the criteria language is clear and the ranges are the same size. This page is the reference we keep open.

Contents

The shape

SUMIF vs SUMIFS

=SUMIF(criteria_range, criteria, sum_range)           one condition; sum_range LAST
=SUMIFS(sum_range, range1, criteria1, range2, criteria2, …)   any number; sum_range FIRST

The two functions put the sum range at opposite ends. Use SUMIFS even for one condition and you’ll never mix them up. Every range must be the same height (and width); a 400-row sum range against a 401-row criteria range returns #VALUE!.

Criteria, one per line

the criteria language

"Groceries"          equals (case-insensitive)
A5                   equals the value in A5
">100"               numbers greater than 100
">="&B2              greater than or equal to the value in B2  ← the & is the whole trick
"<>"&A5              not equal to A5
"<>Done"             not equal to Done (includes blanks — see below)
""                   blank cells (and formulas returning "")
"<>"                 non-blank
"Har*"               starts with Har (text only)
"*tax*"              contains tax
"?"                  exactly one character — so "~?" for a literal question mark

Dates: a range, not a match

this month, this year, last 30 days

=SUMIFS(amt, dates, ">="&DATE(2026,9,1), dates, "<="&EOMONTH(DATE(2026,9,1),0))
=SUMIFS(amt, dates, ">="&A5,             dates, "<="&EOMONTH(A5,0))          A5 = first of month
=SUMIFS(amt, dates, ">="&DATE($B$2,1,1), dates, "<="&DATE($B$2,12,31))        B2 = year
=SUMIFS(amt, dates, ">"&TODAY()-30,      dates, "<="&TODAY())

Two bounds, both built with DATE() or a date cell and joined with &. Never a date typed in the string (">1/9/2026" is locale-dependent), never MONTH(dates)=9 inside SUMPRODUCT when SUMIFS will do — SUMIFS is faster and shows the year. The date formulas guide has the rest of the bounds: month edges with EOMONTH, adding days and months, and the range test on its own.

Two things about one row

person AND type AND status

=SUMIFS(Log!$E$6:$E$205,
      Log!$A$6:$A$205, $A5,          person from this row
      Log!$B$6:$B$205, "Annual",     type from the header
      Log!$F$6:$F$205, "Approved")   only approved

Mixed references make a grid: $A5 for the row label, B$4 for the column header, and the same formula fills a person × type matrix.

Not equal, and why it surprises

"<>Done" sums rows whose status is anything but Done — including empty rows, whose status is blank. On a 150-row task list with 14 tasks, “open tasks” comes out as 150 − done. Either add a guard, or subtract:

two safe forms

=SUMIFS(hours, status, "<>Done", task, "<>")        guard: task must be non-blank
=SUMIF(person, $A5, hours) - SUMIFS(hours, person, $A5, status, "Done")     all minus done

The subtraction reads the same in every app and never counts blanks. It’s what our checklist uses.

Wildcards: part of a name

three wildcards, text only

=SUMIFS(amt, note, "*rent*")        note contains "rent" anywhere
=SUMIFS(amt, code, "INV-????")      exactly four characters after the dash
=SUMIFS(amt, note, "rent*")         starts with "rent"
=SUMIFS(amt, note, "~*")            a literal asterisk, not a wildcard

* stands for any run of characters including none, ? for exactly one, and ~ escapes either when you mean the character itself. All three work the same in Excel and Google Sheets.

They match text only. A numeric invoice number won’t match "INV-*" however it looks on screen — that is the fifth entry in the “returns 0” list below. And a wildcard inside a cell reference has to be joined, not typed: "*"&$B$2&"*", never "*$B$2*".

Another sheet, and the range that doesn’t exist

the sheet name goes on every range

=SUMIFS(Log!$E$6:$E$205, Log!$A$6:$A$205, $A5, Log!$B$6:$B$205, "Annual")

Miss it off one range and the formula quietly sums the wrong column on the current sheet — no error, just a wrong number. That is the failure worth knowing about.

What does not exist is SUMIFS across a stack of sheets. =SUMIFS(Jan:Dec!B:B, …) is not a 3-D reference, it is an error; there is no version of it that works. Two portable ways out:

one per sheet, added

=SUMIFS(Jan!$E:$E, Jan!$A:$A, $A5) + SUMIFS(Feb!$E:$E, Feb!$A:$A, $A5) + …

or — better — one long table with a month column and a single SUMIFS over it. Every workbook we ship is built the second way: twelve sheets that each hold a month are twelve places to fix a formula, and the month is data, not a filename.

OR: add them

SUMIFS is AND. For “Software or Streaming”:

OR across values

=SUMIF(cat, "Software", amt) + SUMIF(cat, "Streaming", amt)
=SUMPRODUCT(SUMIF(cat, {"Software","Streaming"}, amt))     array constant; Excel and Sheets

If the OR is between columns (paid by card OR flagged), the rows that satisfy both would be counted twice — add a helper column that says whether the row qualifies and sum on that.

Greater than a cell, between two cells

numeric bounds from cells

=SUMIFS(amt, amt, ">"&$E$2)                       above a threshold in E2
=SUMIFS(amt, amt, ">="&$E$2, amt, "<="&$E$3)      between E2 and E3
=SUMIFS(amt, days, "<0")                          overdue days (negative)

The sum range and a criteria range may be the same column. ">$E$2" in one string is text, not a reference — it sums nothing and gives no error.

Across a row

SUMIFS works sideways too. Twelve month columns and a row of kinds above them:

sum the columns whose kind is Asset

=SUMIF($B$4:$M$4, "Asset", B6:M6)          kinds in row 4, balances in row 6

That’s how a net-worth snapshot splits assets from liabilities without a column per account.

SUMIF with a whole range as the criterion

look up seven codes and add the hours

=SUMPRODUCT(SUMIF(codes, C6:I6, hours))

SUMIF with a range as its criterion returns one number per cell of that range; SUMPRODUCT adds them. It’s the portable “look up several values and sum the results” — no XLOOKUP, no helper row. Our rota uses it for hours per person per week.

Only the rows a filter left visible

SUMIFS ignores filtering — hide half the rows and the total does not move. SUBTOTAL is the function that respects a filter, but it takes no criteria. Put them together with one helper column:

helper column, then a normal criterion

V6:  =SUBTOTAL(103, $A6)                            1 if this row is visible, 0 if filtered out
   =SUMIFS(amt, V6:V205, 1, cat, "Rent")          filtered total, by category

103 is COUNTA that skips hidden rows. The helper column is one formula filled down, it works in Excel and Google Sheets alike, and it is honest about what it does — unlike AGGREGATE, which does the same thing in one cell and none of it legibly.

Why it returns 0

Numbers stored as text in the sum range or the criteria range. SUMIFS ignores text numbers. Look for left-aligned numbers; fix with VALUE() or multiply by 1.
Trailing spaces in category names — "Groceries " isn’t "Groceries". TRIM() the input or use a dropdown so the values are exact.
A date criterion typed as text, or a date column that’s actually text.
Wildcards on numbers. "*" matches text only; a numeric ID column won’t match.
Ranges of different sizes, or a criteria range on another sheet without the sheet name — silently sums the wrong column.

Why it returns #VALUE!

Ranges of different sizes — count the rows in each. SUMIFS itself is forgiving about text in the sum range (it skips it), but whatever you do with the result next may not be: a "" returned by a formula in a numeric column breaks (1-G)*H downstream. Keep numeric columns numeric — return 0 and hide it with the number format 0;-0;;@. Every workbook we ship follows that rule for exactly this reason.