SUMIFS not working in Google Sheets™? 8 causes + fixes

The safe SUMIFS pattern

If you are summing paid January invoices, write the date criteria as operators joined to real date values:

That pattern avoids most date parsing bugs. The sum range is D:D, the date range is A:A, and the status range is B:B. Every criteria pair is range, criterion.

Cause 1 - Your ranges are different sizes

SUMIFS expects the sum range and every criteria range to be the same shape. This often breaks after someone uses D2:D for the amount column but A:A for the date column.

Find it: compare row counts for each range.

Repeat for each criteria range. If any result is FALSE, fix the range sizes first.

Cause 2 - Date criteria are written as plain text

Dates are the most common SUMIFS trap. A criterion like ">=1/1/2026" can work in some locale settings and fail or behave strangely in others.

Fix: use DATE(year, month, day) or reference a real date cell: ">="&F1.

Cause 2b - You wrapped YEAR(), MONTH(), or another function around the criteria range

If you want to sum a year's worth of amounts, the instinct is to write YEAR(A:A)=2026 as the criterion. It looks correct. It is the most common shape behind the recurring r/googlesheets question "SUMIFS and dates not working right."

SUMIFS does not array-broadcast functions across a criteria range. YEAR(A:A) collapses to a single value, so the criteria range no longer matches the sum range in shape, and you usually get #VALUE! or a silently wrong total. The same trap applies to MONTH, WEEKNUM, TEXT, and any function wrapped around the criteria range.

Fix — option A: express the year as a date range using two criteria pairs, no function wrapping:

Fix — option B: use SUMPRODUCT, which does array-broadcast, when you really need a function-derived condition:

Same idea works for MONTH, WEEKDAY, or anything else you need to derive from the date column.

Cause 3 - Your date column is stored as text

Imported dates can look like dates while actually being text. SUMIFS will not treat text dates the same as real dates, so rows get skipped silently.

Find it: dates are numbers internally. This should be TRUE for real dates:

For the whole column, count likely text dates:

Fix: convert the column with DATEVALUE in a helper column, or use Google Sheets™ data cleanup to split/parse the imported date text.

Cause 4 - Amounts are mixed numbers and text

If the sum range contains numbers mixed with text-stored amounts like "15000", "$15,000", or "15K", your total can be too low without showing an error.

Find it:

If the result is greater than zero and column D should be numeric, those rows need normalization.

Fix: strip symbols and convert to numbers in a helper column. For simple comma/currency cases:

Cause 5 - Hidden spaces in criteria values

"Paid" and "Paid " look the same in a cell, but SUMIFS sees different text. This happens constantly with pasted statuses, customer names, regions, and product codes.

Find it:

Fix: normalize criteria columns with TRIM in helper columns, then run SUMIFS against the cleaned values.

Cause 6 - One criteria cell is blank

If your formula points at a criteria cell, and that cell is blank, SUMIFS searches for blanks. That can return zero or a misleading total.

If F2 is empty, the formula is not "missing a filter"; it is filtering for blank statuses.

Fix: add an explicit guard:

Cause 7 - You need OR logic, but SUMIFS is AND-only

SUMIFS combines criteria with AND logic. This means Status = "Paid" and Status = "Pending" can never both be true for the same row.

For many OR conditions, FILTER or QUERY may be clearer than stacking multiple SUMIFS formulas.

Cause 8 - Imported data includes merged cells, extra headers, or blanks

Reports copied from another tool often include repeated header rows, merged category cells, blank subtotal rows, or footer totals. SUMIFS treats them as data unless you filter them out.

Find it: temporarily filter the source range for blanks in required columns and obvious header text in the middle of the table.

Fix: clean the imported data into a flat table before summing. One header row. One record per row. No merged cells in the calculation range.

Quick diagnostic checklist

Paste these into a helper tab and point the ranges at your actual data:

TL;DR

• Check range sizes first. Every SUMIFS range should cover the same rows and columns.
• Use real dates. Prefer ">="&DATE(2026,1,1) over typed date strings.
• Look for text-stored dates and numbers. They are the biggest silent-skip cause.
• Trim criteria columns. Hidden spaces make matching values fail.
• Remember SUMIFS is AND-only. Add separate SUMIFS formulas for OR logic.
• Use SheetBrain for the data audit. Check catches mixed-type columns and visible formula errors; Generate/Debug helps with formula wording.