Fixing Excel SUMIFS That Returns Zero When Criteria Use Array Constants

You build a clean SUMIFS formula, replace one of the criteria with an array constant like {"North","South"} to catch multiple values at once, and the cell shows zero. The data is clearly there. Nothing is filtered. The formula looks fine. The problem is that SUMIFS doesn't behave the way you expect when you hand it an array.

What's actually happening when SUMIFS gets an array constant

Before diving into fixes, it helps to understand the failure mode precisely. When SUMIFS receives an array constant as a criteria argument, it doesn't return a single number — it returns an array of numbers, one per element in your constant. If you write =SUMIFS(C2:C100, B2:B100, {"North","South"}), Excel internally calculates two partial sums: one for "North" and one for "South". The result is something like {12400, 9800}.

The problem is that a single cell can only display one value. Excel picks the first element of that returned array — but in most formula contexts, without a wrapper that aggregates the array, you see zero or just the first match. In practice, the cell usually shows zero because the implicit intersection fails silently.

What you'll learn

• Why SUMIFS returns zero (not an error) with array constants
• Four proven fixes, from the simplest to the most robust
• How to decide which fix suits your workbook's structure
• Pitfalls that cause each approach to fail

Prerequisites

These solutions work in Excel 2019, Excel 2021, and Microsoft 365. Fix 4 (dynamic array spill) requires Microsoft 365 or Excel 2021. If you're on Excel 2016 or earlier, Fixes 1–3 still apply but the legacy Ctrl+Shift+Enter array entry method is needed for Fix 2.

Why SUMIFS doesn't sum array results automatically

SUMIFS is not an array-aware aggregator by design. It expects each criteria argument to be a single value or a wildcard string, not a multi-element array. When it receives an array, it performs multiple evaluations and returns an array of results. That array then floats up to the cell with no instruction to collapse it into a single total.

This is different from how COUNTIFS behaves in the same scenario — that function has the same limitation, and you can read more about fixing COUNTIFS when criteria use OR logic for a parallel deep-dive. The underlying issue is that Excel's criteria-based functions were built around scalar comparisons, not set membership tests.

The fix in every case is to add a wrapper that forces Excel to sum the array that SUMIFS produces.

Fix 1: Wrap SUMIFS in SUMPRODUCT

This is the most portable fix and works without any special formula entry. SUMPRODUCT is designed to receive arrays, multiply their elements (or just sum them when you give it one), and return a scalar. Wrapping SUMIFS inside it collapses the array automatically.

=SUMPRODUCT(SUMIFS(C2:C100, B2:B100, {"North","South"}))

SUMPRODUCT receives the array {12400, 9800} and sums it to return 22200. No Ctrl+Shift+Enter required, no helper columns, no structural changes to your data.

You can extend this to multiple criteria arguments just as you would with a normal SUMIFS:

=SUMPRODUCT(SUMIFS(C2:C100, B2:B100, {"North","South"}, D2:D100, "Q1"))

Here every row must match one of the region values AND match "Q1" in column D. The SUMIFS evaluates twice (once per region), SUMPRODUCT sums the two partial totals. This covers OR logic on one dimension while keeping AND logic on the others — which is the most common real-world need.

For a broader look at SUMPRODUCT behavior with arrays, the article on fixing SUMPRODUCT when arrays contain text covers some related edge cases worth knowing.

Fix 2: Wrap SUMIFS in SUM as a legacy array formula

Before SUMPRODUCT became the go-to workaround, the traditional approach was to wrap SUM around SUMIFS and enter the formula as a Ctrl+Shift+Enter array formula. The curly braces you see in the formula bar are added by Excel — you don't type them.

{=SUM(SUMIFS(C2:C100, B2:B100, {"North","South"}))}

On Excel 2019 and earlier, press Ctrl+Shift+Enter to confirm. On Microsoft 365, the dynamic array engine handles it without the special keypress, so plain Enter works too.

Functionally this is identical to Fix 1. The difference is maintenance: legacy array formulas are a bit fragile because any collaborator who edits the cell and presses plain Enter on Excel 2019 breaks the array entry. SUMPRODUCT is generally safer for shared workbooks.

Fix 3: Use multiple SUMIFS calls added together

When your array constant is small (two or three values), the most readable approach is to drop the array entirely and add separate SUMIFS calls:

=SUMIFS(C2:C100, B2:B100, "North") + SUMIFS(C2:C100, B2:B100, "South")

This is unambiguous, easy to audit, and requires no knowledge of array formula behavior. Anyone maintaining the workbook will understand it at a glance. The downside is obvious: if your list of criteria grows to six or eight values, the formula becomes unwieldy. At that point, Fix 1 scales far better.

You can also reference individual cells instead of hardcoding the strings, which makes the formula easier to update without touching it directly:

=SUMIFS(C2:C100, B2:B100, E2) + SUMIFS(C2:C100, B2:B100, E3)

Fix 4: Use a helper column to pre-classify rows

When your criteria list is long or changes often, it's sometimes cleaner to pre-classify each row with a helper column rather than building complex array formulas. Add a column that flags whether each row belongs to the group you care about:

=IF(ISNUMBER(MATCH(B2, {"North","South"}, 0)), "Include", "Exclude")

Then your SUMIFS becomes a simple single-criteria formula against that column:

=SUMIFS(C2:C100, F2:F100, "Include")

In Microsoft 365, you can replace the static array with a spill reference to a validation list in another part of the sheet, making the classification fully dynamic. This approach pays off most when the same group definition feeds several downstream formulas — you only update the list in one place.

When to use which fix

Common pitfalls to avoid

Forgetting that SUMPRODUCT also needs consistent range sizes

SUMPRODUCT requires all array arguments to have the same dimensions. If your sum range and criteria range differ in size, you'll get a #VALUE! error. Always make sure C2:C100 and B2:B100 span the same number of rows. This is the same constraint that trips people up in SUMPRODUCT generally — see the article on fixing SUMPRODUCT #VALUE! errors when ranges span multiple sheets for related context.

Hardcoding strings with inconsistent case or spacing

Array constants are case-insensitive in SUMIFS, but leading or trailing spaces will break matches. If your source data has "North " (trailing space) and your array constant has "North", no rows will match for that element. Run a quick TRIM on suspect columns or use TRIM inside a helper column before relying on text criteria.

Mixing AND and OR logic incorrectly

SUMPRODUCT(SUMIFS(...)) handles OR logic on the criteria that receives the array. Every other criteria argument still uses AND logic. If you want OR logic across two different criteria columns simultaneously, the formula gets more complex and you may need nested SUMPRODUCT with Boolean arrays instead. That's a different problem from what's covered here.

Using named ranges that resolve to arrays

If you replace your array constant with a named range that refers to a list (e.g., a column of values), SUMIFS still won't iterate over it automatically. The SUMPRODUCT wrapper fixes this too, but the named range must point to a one-dimensional range for SUMIFS to evaluate each element. A named range used as criteria can introduce its own surprises — it's worth confirming your named range resolves to what you expect before debugging further.

Expecting SUMIFS to deduplicate results

If your array constant contains overlapping values, or if a row can match more than one element, SUMIFS counts that row multiple times — once per matching element. Make sure your array constants contain mutually exclusive values if you want a clean total.

Wrapping up

The root cause here is simple: SUMIFS returns an array when given an array constant, and a lone cell can't display an array. Once you understand that, the fix is obvious — collapse the array with something that can handle it.

Here are your next steps:

• Apply Fix 1 first. Replace =SUMIFS(...) with =SUMPRODUCT(SUMIFS(...)). It takes ten seconds and works in every modern Excel version.
• Check your criteria strings for hidden spaces. Use =LEN(B2) to confirm the character count matches what you expect, or clean the column with TRIM.
• If you have more than four criteria values, consider a helper column approach so the formula stays readable and the list is easy to update.
• Test with a small known dataset before rolling the fix into production reports. Confirm the total manually for one region, then let the formula handle the rest.
• Review related patterns — if you're also filtering by date ranges inside the same formula, check the guide on fixing SUMIFS that returns zero with date criteria in text-formatted cells, since that's a separate but common companion issue.