Fix the 0 error

What 0 means

When SUMIF returns 0, it usually means the criteria did not match any rows, so there was nothing to sum — but the formula is silently treating every comparison as false. This is different from an error: the formula runs successfully but finds no matches, or matches rows whose sum_range cells contain text numbers that do not add up.

Common causes

• Numbers in the sum_range are stored as text (imported from a CSV or another system) — SUMIF adds them as 0 because text does not contribute to a numeric sum.
• The criteria string does not match the actual cell content due to a data-type mismatch — the criteria is the number 42 but the range contains the text '42'.
• Extra spaces in the criteria_range cells or in the criteria value prevent a string match.
• The criteria_range and sum_range are different sizes — SUMIF sums the rows that correspond to the matched rows, and if the ranges are misaligned the returned rows are wrong.
• A wildcard or comparison operator in the criteria is formatted incorrectly, e.g. writing >10 without quotes: the correct syntax is ">10" (as a string in quotes).

Example fix

In this case TRIM removes any trailing space in the criteria literal; the same fix should be applied to source data if spaces are embedded in B2:B100 using a helper column or TRIM in an array context.

How to fix it

1. 1Check whether sum_range values are real numbers: select a cell and look at the alignment (text left-aligns, numbers right-align by default) or use =ISNUMBER(B2). Convert text-numbers with Paste Special > Multiply in Excel, or =VALUE(B2) in a helper column.
2. 2Make sure the criteria type matches the range type. If the range has text '42' use criteria "42"; if it has the number 42 use criteria 42 without quotes.
3. 3Use TRIM on the criteria: wrap the criteria cell reference in TRIM(A2) to eliminate leading/trailing spaces.
4. 4Ensure criteria_range and sum_range are the same dimensions. Both should cover the same number of rows, e.g. $B$2:$B$100 and $C$2:$C$100.
5. 5Put comparison operators inside a quoted string: =SUMIF(B:B,">10",C:C) — not =SUMIF(B:B,>10,C:C) which is a syntax error.

Frequently asked