Fixing Excel SUMIFS That Returns Zero When Criteria Range Contains Error Values

Your SUMIFS formula has been working fine for weeks. Then someone pastes a new batch of data, a few #N/A errors appear in your lookup column, and suddenly every SUMIFS that touches that column returns zero. The formula is not broken — the data is. But SUMIFS won't tell you that directly.

This article explains exactly how error values short-circuit SUMIFS evaluation and walks you through four practical fixes, ranging from a one-liner wrapper to a proper data-cleaning approach.

What You'll Learn

• Why a single error value in a criteria range can silence an entire SUMIFS result
• How to reproduce and diagnose the problem reliably
• Three formula-based fixes you can apply without touching your source data
• How to clean the criteria range upstream so the problem doesn't recur
• Which approach to choose depending on your spreadsheet structure

Why SUMIFS Silently Returns Zero

When Excel evaluates a SUMIFS formula, it compares every cell in the criteria range against your criteria value. If a cell in that range contains an error — #N/A, #VALUE!, #REF!, #DIV/0!, or any other — Excel cannot compare it. Instead of skipping the error, Excel treats the comparison as unresolvable and that row fails to match.

That behavior alone wouldn't cause a zero result. The real problem is that the criteria range and the sum range are evaluated together positionally. If error values occupy rows that hold the values you want to sum, those rows are permanently excluded. In the worst case, every matching row contains an error in the criteria column, and your total comes back as zero.

This is different from errors in the sum range, where only the errored cells are skipped. An error in the criteria range disqualifies the entire row from the match calculation. The distinction matters for choosing the right fix.

How SUMIFS Evaluates Criteria Ranges

SUMIFS builds an internal array of TRUE/FALSE values — one per row — by testing each criteria range against its paired criteria. It then multiplies all those boolean arrays together and sums only the rows where every test returned TRUE. An error value in any criteria range cell produces neither TRUE nor FALSE; it propagates as an error into that position of the boolean array, which Excel resolves to FALSE when summing.

The key insight: errors don't cause SUMIFS itself to return an error. They cause individual rows to silently fail the match test. That's why you get a clean-looking zero instead of a red #N/A that would alert you immediately.

If you've ever encountered a similar silent failure with a different function, the same principle applies to SUMPRODUCT when criteria reference problem cells — the root cause is always an unresolvable comparison in the criteria evaluation step.

Reproducing the Problem

Set up a small test case before applying any fix. This confirms the error values are actually the cause and not something else (like a data-type mismatch, which has its own separate cause explained in the article on text-formatted numbers in SUMIFS sum ranges).

1. Create a three-column table: Category (A), Lookup Result (B), Amount (C).
2. Put some valid text in B2:B5, but put =NA() in B3 and B5 to simulate errors.
3. Enter a SUMIFS that matches on column B: =SUMIFS(C2:C5, B2:B5, "Alpha").
4. Observe that the formula returns 0 even if some non-error rows hold "Alpha".

Now replace the =NA() cells with a placeholder like "ERROR" and re-evaluate. The formula starts returning correct totals. That confirms the errors — not the values — are the problem.

Fix 1: Wrap the Criteria Range with IFERROR

The simplest fix converts every error in the criteria range to a value that will never match your criteria. Wrapping the range in IFERROR inside SUMIFS works because Excel evaluates the wrapper before running the comparison.

=SUMIFS(C2:C100, IFERROR(B2:B100, ""), "Alpha")

Any error cell in B2:B100 becomes an empty string. Since your criteria value is "Alpha", those rows never match — which is exactly what you want. Rows with valid values are unaffected.

Important: This syntax requires you to enter the formula as an array formula in older versions of Excel (pre-Microsoft 365 / Excel 2019). Press Ctrl+Shift+Enter instead of just Enter. In Microsoft 365 and Excel 2021, dynamic arrays handle it automatically.

Check whether it's working as an array formula by looking for curly braces in the formula bar: {=SUMIFS(...)}. If they're missing in an older Excel version, delete the formula and re-enter it with Ctrl+Shift+Enter.

Fix 2: Use IF + ISNUMBER to Screen Out Errors

If your criteria range contains numeric lookup results (like IDs or codes returned by VLOOKUP), use ISNUMBER to explicitly screen out non-numeric values before the comparison runs. This is more targeted than IFERROR and makes the intent clearer to the next person reading the formula.

=SUMPRODUCT((ISNUMBER(B2:B100))*(B2:B100=42)*C2:C100)

Here the first factor (ISNUMBER(B2:B100)) returns 0 for any error or text cell, effectively zeroing out those rows before the match on 42 runs. SUMPRODUCT handles array math natively, so no Ctrl+Shift+Enter is needed.

For text criteria, swap ISNUMBER for ISTEXT:

=SUMPRODUCT((ISTEXT(B2:B100))*(B2:B100="Alpha")*C2:C100)

This approach also gives you a natural place to add additional criteria — just multiply in another boolean array for each condition.

Fix 3: Clean the Source Data with a Helper Column

Formula wrappers work, but they add complexity that can confuse colleagues or break when someone copies the formula partially. A cleaner long-term fix is a dedicated helper column that converts errors to a neutral value before SUMIFS ever sees them.

Add a column D next to your criteria range and enter:

=IFERROR(B2, "")

Copy this down the full range, then rewrite your SUMIFS to reference column D instead of column B:

=SUMIFS(C2:C100, D2:D100, "Alpha")

Your SUMIFS stays simple. The error-handling logic is isolated in the helper column, which is easy to spot and maintain. If the source data changes and errors disappear, the helper column silently passes through the real values without any adjustment needed.

This is the approach to take when the spreadsheet is shared with non-technical users who need to maintain it themselves, or when the criteria range is referenced by multiple formulas across the workbook.

Fix 4: Use SUMPRODUCT for Full Control

When SUMIFS is returning zero and you want maximum transparency, SUMPRODUCT gives you full visibility into what each part of the formula is doing. You can evaluate each factor separately using F9 in the formula bar to see the intermediate arrays.

=SUMPRODUCT(
  IFERROR((B2:B100="Alpha"), 0) *
  (A2:A100="East") *
  C2:C100
)

The outer IFERROR wraps only the criteria comparison that touches the problematic column. If B2 contains an error, IFERROR((B2="Alpha"), 0) returns 0 for that row instead of propagating the error. The other criteria (column A check) and the sum range (C) are unaffected.

This pattern also handles multiple criteria ranges where different columns might have errors in different rows. Wrap each potentially-errored comparison in its own IFERROR(..., 0).

Choosing the Right Fix

Common Pitfalls to Avoid

Forgetting array entry in older Excel. The IFERROR-inside-SUMIFS trick works without array entry only in Excel versions that support implicit intersection or dynamic arrays. In Excel 2016 or earlier, you must press Ctrl+Shift+Enter, or the formula evaluates only the first cell in the range and returns a misleading result.

Replacing errors with a value that matches your criteria. If you convert errors to zero (IFERROR(B2:B100, 0)) and your criteria is also zero, those error rows will now match. Always use a sentinel value that cannot appear in valid data — an empty string works for text columns, and a clearly-out-of-range number works for numeric ones.

Confusing criteria range errors with sum range errors. If errors are in your sum range (column C), SUMIFS skips those cells individually but still sums the rest. That produces a low total, not a zero. The fix there is different — usually SUMPRODUCT(IFERROR(C2:C100,0)*...). If you're unsure which column is causing the problem, audit each range separately with =ISNUMBER(MATCH(TRUE, ISERROR(B2:B100), 0)) entered as an array formula.

Not checking for errors introduced upstream. If your criteria column is populated by VLOOKUP or INDEX/MATCH, those are the likely source of errors. Fixing the SUMIFS is a patch; fixing the lookup formula is the cure. The article on INDEX MATCH returning #N/A due to leading zeros covers one common reason lookups silently fail and generate errors downstream.

Mixed error types behaving differently. #N/A is the most common error from failed lookups, but #REF! and #VALUE! behave the same way in a criteria range. A single IFERROR catches all of them, so you don't need to handle error types individually.

For a related failure mode involving the wrong total rather than a zero result, the guide on SUMIFS returning wrong totals when the sum range has merged cells covers that scenario in detail.

Wrapping Up

Error values in a SUMIFS criteria range cause silent row exclusions, not a visible formula error. That's what makes this bug tricky — the formula looks healthy and returns a number, just the wrong one.

Here are your concrete next steps:

1. Audit your criteria ranges. Use =ISNUMBER(MATCH(TRUE, ISERROR(B2:B100), 0)) (array-entered) to confirm whether any errors exist in each criteria column before applying a fix.
2. Apply the IFERROR wrapper if you're on Microsoft 365 and want a quick formula-level fix without restructuring your sheet.
3. Switch to a helper column if this spreadsheet is shared or if multiple formulas reference the same problem range.
4. Trace errors back to their source. If errors come from upstream VLOOKUP or INDEX/MATCH formulas, fix the lookup logic rather than masking errors in every downstream formula.
5. Add data validation or conditional formatting to the criteria column to flag future errors before they silently corrupt your totals.