Fixing Excel SUMIFS That Counts Blank Cells When Criteria Range Has Mixed Data Types

You set up a SUMIFS formula, the range looks right, the criteria look right — and yet the total is wrong. Rows with blank criteria cells are being included, or numbers that should match aren't matching at all. The culprit almost always lives in the criteria range: a mix of true numbers, text-formatted numbers, and genuine blanks living side by side.

What You'll Learn

• Why SUMIFS treats text-stored numbers and real numbers as different values
• How blank cells interact with criteria strings like "" and <>""
• Four concrete fixes you can apply right now, from formula tweaks to data cleanup
• How to use SUMPRODUCT as a type-safe alternative when SUMIFS won't cooperate

Why SUMIFS Misbehaves With Mixed Data Types

Excel's SUMIFS function matches criteria by comparing each cell in the criteria range against your criteria value. The comparison is strict about data type: the number 1 does not equal the text "1", and an empty string "" does not equal a truly empty cell. When a column contains a mix of real numbers, text-formatted numbers imported from a CSV or database, and blank cells, SUMIFS can silently include or exclude rows you didn't intend.

This is not a bug — it's how Excel's comparison engine works. Understanding the rules lets you write formulas that behave predictably regardless of what landed in your spreadsheet.

Understanding How SUMIFS Evaluates Criteria

Before fixing anything, it helps to know exactly what SUMIFS does under the hood. For each row, Excel checks whether the criteria range cell matches the criteria expression. The matching rules are:

• Exact match (number): Criteria 5 matches only cells that contain the numeric value 5.
• Exact match (text): Criteria "5" matches only cells that contain the text string "5". A real numeric 5 does not match.
• Blank match: Criteria "" matches cells that are truly empty and cells that contain an empty string returned by a formula (e.g., =IF(A1=0,"",A1)).
• Non-blank match: Criteria <>"" excludes truly empty cells, but it includes cells containing the number 0 and cells containing a formula that returns an empty string — which surprises most people.

That last point is the most common source of wrong totals. If you use <>"" expecting to filter out blanks, you will accidentally include formula-generated empty strings, and you may exclude numeric zeros.

The Blank Cell Problem: What's Really Happening

Imagine column B holds a product category. Some rows have a real text value like "Widget", some are genuinely empty, and some contain a formula that returns "" when a condition isn't met. Your formula is:

=SUMIFS(C2:C100, B2:B100, "Widget")

This works correctly for real text. The trouble starts when you add a second criteria pair to exclude blanks:

=SUMIFS(C2:C100, B2:B100, "Widget", A2:A100, "<>")

The criteria "<>" is shorthand for "not empty," but Excel interprets it as "not equal to the empty string." A cell containing a formula result of "" evaluates as equal to the empty string, so it gets excluded — as intended. But a cell containing the number 0 also gets excluded, because 0 is not the empty string. Meanwhile, a cell whose formula returned " " (a single space) is not empty either, so it passes the filter even though it looks blank to a human.

For a related look at how criteria logic interacts with filtering, see fixing COUNTIFS that returns wrong counts with OR logic — many of the same principles apply when building multi-condition formulas.

The Text-vs-Number Mismatch Problem

Suppose column A holds order IDs. Half came from a database export as real integers; the other half were pasted from a web portal and landed as text. You want to sum column C wherever the order ID equals 1042:

=SUMIFS(C2:C100, A2:A100, 1042)

This only sums rows where A holds the number 1042. Any row where A holds the text "1042" is skipped. The total is wrong and there's no error to clue you in.

The same mismatch appears in reverse. If your criteria cell contains a text "1042" (maybe it was typed with a leading apostrophe or populated by a text formula), and the data column holds real numbers, nothing matches and SUMIFS returns zero. This is closely related to the offset mismatch issue covered in fixing SUMIF that returns zero when the sum range offset doesn't match.

How to Diagnose Your Criteria Range

Before writing any fix formula, confirm exactly what types are in play. Select a blank cell next to your criteria range and enter:

=ISNUMBER(A2)

Drag it down alongside your criteria column. If you see a mix of TRUE and FALSE in what should be a uniform numeric column, you have the text-vs-number problem. For blanks, use:

=LEN(A2)

A truly empty cell returns 0. A cell containing a space or an empty-string formula result also returns 0 for LEN — unless the space is there, in which case it returns 1. Use =ISBLANK(A2) to distinguish a genuinely empty cell (TRUE) from a formula that returns "" (FALSE).

Once you know whether you have type mismatches, blanks, or formula-generated empty strings, you can pick the right fix.

Fix 1: Normalize the Criteria Range With ISNUMBER or VALUE

The cleanest fix when the criteria range has text-stored numbers is to convert them in place. Select the column, go to Data > Text to Columns, click Finish without changing settings, and Excel converts text-numbers to real numbers in one step. No formula needed.

If you can't modify the source data (because it's refreshed from a query or shared workbook), coerce the criteria at the formula level using a helper column:

=VALUE(A2)

Fill this down alongside your data, then point your SUMIFS at the helper column instead of the raw column. The helper column always produces a real number (or an error for non-numeric text, which you can wrap in IFERROR to return 0).

Fix 2: Use SUMPRODUCT for Explicit Type Control

SUMPRODUCT gives you direct control over every comparison, including type coercion. When SUMIFS is giving you inconsistent results due to mixed types, rewriting as SUMPRODUCT is often the most reliable path:

=SUMPRODUCT((VALUE(A2:A100)=1042) * (C2:C100))

Here VALUE(A2:A100) converts the entire range to numbers before comparing. Text that can't convert to a number becomes an error, which evaluates as FALSE (0) in the multiplication, so it's automatically excluded from the sum.

To also exclude genuinely blank rows in the criteria range:

=SUMPRODUCT((A2:A100<>"") * (VALUE(IF(A2:A100<>"", A2:A100, 0))=1042) * (C2:C100))

The first factor (A2:A100<>"") masks out blank and formula-empty cells before the VALUE conversion runs, avoiding errors on empty inputs. This approach is explored in more depth in the article on fixing SUMPRODUCT that returns wrong totals when arrays contain text.

Fix 3: Coerce Criteria With --() or VALUE() Wrapping

If you'd rather keep the SUMIFS structure and only need to handle the case where your criteria value (not the whole range) might be a text number, wrap the criteria cell reference with double-unary coercion:

=SUMIFS(C2:C100, A2:A100, --E2)

The -- operator converts the text "1042" in E2 to the number 1042, so SUMIFS now looks for the numeric value. This works when the criteria cell is the source of the mismatch — for example, a criteria cell populated by a formula that returns text.

The limitation: this only coerces the criteria value, not every cell in the criteria range. If the criteria range itself has mixed types, you still need Fix 1 or Fix 2.

Fix 4: Clean the Source Data Before Summing

Sometimes the most durable fix is upstream. If your data arrives from Power Query, add a Change Type step in the query to explicitly cast every column to its intended type. Power Query applies this on every refresh, so the problem never reaches your worksheet formulas.

For manually maintained sheets, protect the column with Data Validation set to "Whole Number" so users can't accidentally enter text. Add a conditional formatting rule that highlights cells where ISNUMBER returns FALSE — this gives you a visual alert the moment mixed types appear.

If blank cells are the root cause, consider whether those rows should exist at all. A lookup formula that returns "" for unmatched rows can be changed to return "N/A" or a sentinel value instead, making it unambiguous in your SUMIFS criteria. You might also find it useful to review how SUMIF handles hidden rows in a criteria range, since hidden rows introduce similar invisible-data surprises.

Common Pitfalls to Avoid

• Using "" as a criteria to match blanks: This matches both truly empty cells and formula-generated empty strings. If you need only truly empty cells, SUMPRODUCT with ISBLANK is more precise.
• Assuming <>"" excludes all blanks: It excludes empty strings and formula-returned "", but zero values in numeric columns pass through this filter — which can inflate your total.
• Mixing coercion strategies across a workbook: If some formulas use -- and others use VALUE() and others use Text to Columns, your workbook becomes hard to audit. Pick one approach and document it.
• Forgetting that imported data refreshes reset your manual fixes: If you convert text-numbers with Text to Columns but the query refreshes and overwrites those cells, your fix disappears. Always push type coercion into the query or a persistent helper column.
• Testing with small data that doesn't expose the edge case: A criteria range with only real numbers will work perfectly. The bug only surfaces after a data refresh or paste that introduces a few text-formatted values. Test with ISNUMBER checks on every criteria range before deploying a formula to production use.

Wrapping Up

Mixed data types in a criteria range are one of the quietest ways for a SUMIFS formula to go wrong — no error, just a subtly incorrect total. Here are the concrete next steps to take right now:

1. Run =ISNUMBER() and =ISBLANK() checks on every column you use as a criteria range to confirm the actual types present.
2. Use Data > Text to Columns to convert text-stored numbers to real numbers if you can modify the source data.
3. Switch to SUMPRODUCT with explicit VALUE() coercion when you need type-safe matching without touching the underlying data.
4. Apply double-unary -- coercion to criteria cell references when the mismatch is on the criteria side rather than the data side.
5. Move type enforcement upstream into Power Query or Data Validation so the problem can't recur after the next data refresh.