Fixing Excel SUMIF That Returns Zero When Criteria Look Correct

You write a SUMIF formula, double-check the range, triple-check the criteria, and it still returns zero. The values you expect to be summed are sitting right there in plain sight. This is one of the most frustrating Excel problems precisely because the formula looks correct — and usually is syntactically correct. The bug is almost always in the data itself.

What You'll Learn

• How to identify whether numbers are secretly stored as text
• How trailing spaces silently break criteria matching
• Why a data type mismatch between your criteria and your range causes zero results
• How to fix an offset or wrong-sized sum range
• How to handle dates and hidden non-printable characters

Why SUMIF Returns Zero (The Short Version)

SUMIF compares each cell in your criteria range against your criteria value. If nothing matches — even for a reason you can't see — it returns zero. It does not throw an error. It just silently sums nothing.

The root causes split into two buckets: data quality issues (spaces, text-formatted numbers, hidden characters) and formula construction issues (wrong range size, type mismatch in the criteria argument, wildcard misuse). Work through each cause below in order; most people find the culprit by the third section.

Cause 1: Numbers Stored as Text

This is the most common cause. When you import data from a CSV, a database export, or a web scrape, numbers often land in Excel as text strings. A cell showing 1500 might actually contain the text "1500", not the number 1500. SUMIF treating your criteria range as numbers will never match text cells, and vice versa.

How to spot it: Select a cell that should contain a number. If it left-aligns by default (without manual formatting), it is stored as text. You may also see a small green triangle in the top-left corner of the cell. Or run this quick check:

=ISNUMBER(A2)

If that returns FALSE for a cell that looks like a number, you have text-formatted numbers.

How to fix it: Select the affected column, go to Data > Text to Columns, click Finish without changing anything, and Excel will coerce the values to real numbers. Alternatively, type 1 in an empty cell, copy it, select your text-number range, and use Paste Special > Multiply. This forces numeric conversion in place.

You can also use a helper column with =VALUE(A2) and use that column as your criteria range instead.

Cause 2: Trailing or Leading Spaces in Criteria or Data

A cell containing "Apple " (with a trailing space) will never match a criteria string of "Apple". This is especially common in data copied from other systems or manually entered over time. You cannot see the space, so the data looks perfectly correct.

Check for spaces using LEN:

=LEN(A2)

If a cell that should contain Apple (5 characters) returns 6 or more, there are extra spaces. You can also compare:

=A2=TRIM(A2)

If that returns FALSE, the cell has leading or trailing spaces.

How to fix it: Use TRIM in a helper column, then paste-as-values to replace the original data. If you want a quick SUMIF that ignores trailing spaces without cleaning the source data, wrap your criteria in TRIM inside the formula — but note that SUMIF's criteria argument is evaluated as a literal string, so you need to restructure it slightly:

=SUMPRODUCT((TRIM(A2:A100)="Apple")*B2:B100)

This approach trades SUMIF for SUMPRODUCT and applies TRIM across the entire criteria range before matching. It is slower on very large datasets, but it works. For details on similar data-cleaning pitfalls, see how trailing spaces break VLOOKUP results — the same root cause applies here.

Cause 3: Criteria Data Type Mismatch

Your criteria argument and the values in your criteria range must be the same data type for a match to occur. If your criteria range contains numbers and you pass a text string as the criteria, you get zero. The reverse is equally true.

Consider this formula:

=SUMIF(A2:A100,"123",B2:B100)

If column A holds numeric 123 values, this formula returns zero because "123" (text) does not equal 123 (number). Drop the quotes to fix it:

=SUMIF(A2:A100,123,B2:B100)

If your criteria comes from a cell reference (e.g., D1), check whether D1 holds a number or text using =ISNUMBER(D1). You can force the criteria cell to be numeric with =VALUE(D1) if needed.

The same type-mismatch issue appears in Pandas when grouping mixed-type columns — if you have worked through GroupBy silently ignoring NaN values, the diagnostic mindset is nearly identical.

Cause 4: The Sum Range Is the Wrong Size or Offset

SUMIF has a built-in behavior that catches many people off guard: if your sum_range is a different size than your criteria_range, Excel does not throw an error. Instead, it implicitly resizes the sum range to match the criteria range, starting from the top-left cell of the sum range you specified.

This means a formula like this:

=SUMIF(A2:A100,"Apple",C2:C10)

...will silently extend the sum range to C2:C100, which may not be what you intended. If the actual data you want to sum is in a different area, you can end up summing the wrong cells — or empty cells — and getting zero.

Best practice: Always make your criteria_range and sum_range the same dimensions and starting row. Use named ranges or structured table references to make this automatic.

Also double-check that your sum range column actually contains numbers, not formulas that return empty strings (""). A cell showing blank because of an IF formula returning "" contributes zero to the sum, but it is easy to mistake for a real zero.

Cause 5: Wildcard Characters Used Incorrectly

SUMIF supports two wildcards: * (any sequence of characters) and ? (any single character). If your actual data contains literal asterisks or question marks, this causes unexpected behavior — the wildcard is interpreted as a pattern, not a literal character.

For example, if your criteria range contains the string Part*A and you try to match it with "Part*A", SUMIF treats the * as a wildcard and matches anything starting with Part and ending with A, not the literal string.

To match a literal asterisk or question mark, escape it with a tilde:

=SUMIF(A2:A100,"Part~*A",B2:B100)

Conversely, if you intend to use a wildcard for partial matching but forget to include it, you may get zero results. To sum all rows where column A contains the word "Apple" anywhere in the cell:

=SUMIF(A2:A100,"*Apple*",B2:B100)

Without the asterisks, only cells with exactly Apple (nothing before or after) will match.

Cause 6: Dates That Look Like Dates But Aren't

Dates in Excel are stored as serial numbers internally. When you import date data from an external system, the dates sometimes arrive as text strings like "2024-01-15" rather than real Excel date values. A SUMIF using a real date criteria against a text-date column will always return zero.

Test whether a date cell is real by checking =ISNUMBER(A2). A genuine Excel date returns TRUE (because it is stored as a number under the hood). A text date returns FALSE.

To convert text dates to real dates, use DATEVALUE:

=DATEVALUE(A2)

Apply this in a helper column, paste as values, and format the column as a date. For a deeper look at Excel date handling, converting timestamps to time values in Excel covers related conversion techniques you can adapt here.

Also check that your criteria date is genuinely a date value. If you type a date directly into the criteria argument as a string — "2024-01-15" — Excel may or may not coerce it depending on your locale. It is safer to reference a cell that contains a real date, or use DATE(2024,1,15) as your criteria.

Cause 7: Hidden Characters Beyond Normal Spaces

Sometimes data imported from web pages or legacy systems contains non-printable characters — things like non-breaking spaces (CHAR(160)), line feeds (CHAR(10)), or other control characters. These are invisible, and TRIM does not remove them because TRIM only handles standard space characters (CHAR(32)).

To check for hidden characters, compare the LEN of the cell to the LEN after cleaning:

=LEN(A2)-LEN(CLEAN(TRIM(A2)))

If that returns anything other than zero, there are characters that TRIM and CLEAN are stripping out. The CLEAN function removes non-printable characters defined in the 7-bit ASCII set.

For non-breaking spaces specifically, you need to substitute them out manually:

=SUBSTITUTE(A2,CHAR(160),"")

Combine TRIM, CLEAN, and SUBSTITUTE in a helper column to produce a fully cleaned version of your criteria range, then run your SUMIF against that.

Common Pitfalls at a Glance

If you frequently deal with messy imported data before running aggregations, the same diagnostic discipline applies when you reshape data with Power Query — cleaning at the query stage prevents these issues from reaching your formulas in the first place.

Wrapping Up

A SUMIF returning zero when the data looks correct is almost always a data quality issue, not a formula syntax error. Here are the concrete steps to take right now:

1. Run =ISNUMBER() on a sample of your criteria range cells to confirm they are actual numbers if you expect numbers.
2. Run =A2=TRIM(A2) on a few cells in your criteria range to catch trailing or leading spaces.
3. Check your sum range dimensions match your criteria range exactly — same start row, same number of rows.
4. If you use wildcards, verify that literal asterisks or question marks in your data are escaped with a tilde (~).
5. For persistent mismatches, use =LEN(A2)-LEN(CLEAN(TRIM(A2))) to detect hidden non-printable characters, then clean with SUBSTITUTE(A2,CHAR(160),"") as needed.

Once you identify the source of the mismatch, the fix is usually a helper column, a Text to Columns conversion, or a structural table reference. None of these take more than a few minutes once you know where to look.