Fixing Excel SUMPRODUCT That Returns Zero When Criteria Reference Merged Cells

You've built a SUMPRODUCT formula that should sum a column based on a category label, but the result is zero. The data is there, the formula logic looks correct, and yet — nothing. If your criteria column uses merged cells to group rows visually, that's your culprit.

Merged cells are one of the most common hidden traps in Excel spreadsheets. They look like they span multiple rows, but internally Excel only stores the value in the top-left cell of the merge. Every other cell in the merged group reads as empty, and SUMPRODUCT's array engine sees those empty cells exactly as they are.

What You'll Learn

• Why merged cells cause SUMPRODUCT to return zero even when data is present
• How to reproduce and diagnose the problem quickly
• Four concrete fixes, from unmerging to formula-only workarounds
• How to handle the same issue when merged cells are in the sum range, not the criteria range
• Common mistakes people make right after they think they've fixed it

Why SUMPRODUCT Returns Zero with Merged Cell Criteria

Before diving into fixes, it's worth being precise about the failure mode. SUMPRODUCT builds an array from each range you give it, then multiplies those arrays element-by-element and sums the result. When you use it for conditional summing — like =SUMPRODUCT((A2:A20="Widgets")*(C2:C20)) — the first array is a boolean array of TRUE/FALSE (1/0) values.

If column A has merged cells, only the first cell of each merged group contains "Widgets". Cells A3, A4, A5 (merged visually under A2) actually contain empty strings. So your boolean array looks like {1, 0, 0, 0, ...} instead of {1, 1, 1, 1, ...}, and the sum is dramatically under-counted — or exactly zero if the value only appears in row 2 but the matching data is in rows 3 through 5.

Reproducing the Problem

Set up a small test to confirm this is really what's happening in your sheet. In a new sheet, create the following data:

• Merge cells A2:A4 and type Widgets
• Merge cells A5:A7 and type Gadgets
• Put sales values 100, 200, 300, 400, 500, 600 in C2:C7

Now enter this formula in an empty cell:

=SUMPRODUCT((A2:A7="Widgets")*(C2:C7))

You'd expect 600 (100+200+300). You'll get 100 — because only A2 passes the test. Then click on cell A3. The formula bar shows it's empty. That confirms the diagnosis.

You can also verify with a quick COUNTIF: =COUNTIF(A2:A7,"Widgets"). If it returns 1 instead of 3, merged cells are definitely the problem.

Fix 1: Unmerge and Fill Down

This is the cleanest fix and the one you should use if you control the sheet's structure. Merged cells cause problems across nearly every lookup and aggregation function — not just SUMPRODUCT. Removing them and repeating the value in each row is best practice for any data range you intend to calculate against.

1. Select the merged cells in your criteria column (or the entire column).
2. Go to Home → Merge & Center → Unmerge Cells.
3. With the same range still selected, press F5 → Special → Blanks → OK.
4. Without clicking anywhere, type = then press the Up arrow key once, then press Ctrl+Enter.

That fills every blank cell created by unmerging with a reference to the cell above it. Then paste-as-values to lock in the text:

Select column A → Copy → Paste Special → Values → OK

Your SUMPRODUCT formula will now return the correct total with no other changes required.

If you're worried about losing the visual grouping that merged cells provided, use Center Across Selection instead (Format Cells → Alignment → Horizontal → Center Across Selection). It looks almost identical to a merge but doesn't collapse cell values.

Fix 2: Use a Helper Column to Replicate Merge Values

Sometimes you can't modify the source data — it's a shared file, a protected sheet, or a report generated by another system. In that case, create a helper column that fills in the missing values without touching the original merged cells.

Add a column (say column B) next to your merged criteria column. In B2, enter the value directly or reference A2. In B3 onward, use this pattern:

=IF(A3<>"",A3,B2)

Copy that down through the entire range. The formula reads: if the cell in column A has a value, use it; otherwise carry forward whatever was in B above. Since merged cells leave everything below the first cell empty, this effectively fills the gaps.

Now rewrite your SUMPRODUCT to reference column B instead of column A:

=SUMPRODUCT((B2:B7="Widgets")*(C2:C7))

This returns the correct result and leaves the original data completely untouched. If the source data refreshes periodically, the helper column updates automatically.

Fix 3: Wrap the Criteria Range with IF and IFERROR

If you can't add a helper column and need a pure in-formula solution, you can approximate the fill-down behavior inside the SUMPRODUCT expression itself — but it requires a slightly different approach. This works when the merged cells follow a predictable pattern (each group is contiguous and blank cells are always below the label).

The idea is to use LOOKUP to carry the last non-blank value forward within the array. Here's a formula that does it:

=SUMPRODUCT(
  (LOOKUP(ROW(A2:A7),IF(A2:A7<>"",ROW(A2:A7)),A2:A7)="Widgets")
  *(C2:C7)
)

Breaking this down: IF(A2:A7<>"",ROW(A2:A7)) builds an array of row numbers where column A is non-blank, and FALSE elsewhere. LOOKUP then finds the largest row number that is less than or equal to the current row's number, and returns the value at that position. This is Excel's classic "last non-blank above" trick applied inside an array context.

This is more complex and harder to maintain, so only use it when Fixes 1 and 2 are genuinely off the table. If you're on Excel 365, you can also use SCAN and LAMBDA for a cleaner version of the same logic, but that's a topic in itself.

Fix 4: Use SUMIFS Instead of SUMPRODUCT for Simple Cases

If your original SUMPRODUCT formula was doing simple conditional summing (one or two criteria, no multiplication of multiple arrays), SUMIFS can replace it and handles most single-condition sums. That said, SUMIFS has the same problem with merged cells — it also sees only the top-left cell value. So this fix only applies if you combine it with the helper column from Fix 2.

With the helper column in place, this is cleaner than SUMPRODUCT for a straightforward sum:

=SUMIFS(C2:C7, B2:B7, "Widgets")

For more context on how SUMIFS behaves with tricky criteria ranges, the article on fixing SUMIFS that returns zero when criteria use array constants covers several related edge cases worth reading alongside this one.

Handling Merged Cells in the Sum Range

Everything above assumes merged cells are in the criteria column. But sometimes the column you're summing is itself merged. This is a different problem with a different failure mode.

When SUMPRODUCT tries to sum a range where cells are merged, it picks up the value in the top cell of each merged group and treats the remaining cells as zero. A merged group covering C2:C4 with value 300 doesn't mean 300 appears in each of C2, C3, and C4 — it means C2 contains 300 and C3:C4 contain zero. Your sum will be lower than expected.

The fix is the same: unmerge and fill down, but this time you need to decide what each cell should actually contain. If the merged value of 300 represents a total that should be repeated in each row, fill with 300. If it represents a value that only belongs to one row, leave the others as zero or actual row-level data.

For a related scenario involving lookup functions and merged search arrays, see this guide on XLOOKUP returning the wrong value when the search array has merged cells — the underlying cause is identical, and the diagnostic steps transfer directly.

Common Pitfalls After Fixing the Merge

Even after you unmerge and fill down, a few things can still leave you with a zero result. Watch out for these:

• Leading or trailing spaces in the filled values. When you paste-as-values after a fill-down, the text may carry whitespace from the original entry. Wrap your criteria in TRIM inside the formula, or clean the column with =TRIM(A2) first.
• Case sensitivity isn't usually the issue, but inconsistent capitalization ("widgets" vs "Widgets") can matter if you later switch to EXACT-based comparisons. Standardize casing during cleanup.
• The fill-down creates formula cells, not text. If you forget the paste-as-values step, the filled cells contain =A2 style references. If the source data changes, the fill could produce unexpected values. Always convert to values.
• Number-stored-as-text in the sum range. This is a separate but related problem. If unmerging exposed cells that were formatted as text, SUMPRODUCT will skip them. Check with =ISNUMBER(C3) for a quick test.

The merged-cell problem often co-occurs with other data quality issues. The article on SUMIFS counting blank cells when criteria ranges have mixed data types walks through what to do when your criteria range has both text and numbers after a cleanup pass.

Next Steps

Here are four concrete actions to take after you've read this:

1. Audit your criteria and sum ranges for merged cells before building any new SUMPRODUCT formula. Use Find & Select → Go To Special → Blanks after selecting the range to spot hidden empties instantly.
2. Replace merged cells with Center Across Selection on any sheet used for calculation. It preserves the visual look without breaking formulas.
3. Add a helper column as a default practice in shared workbooks where you can't guarantee the source data structure won't include merges.
4. Test your SUMPRODUCT result against a manual sum of a small subset whenever your sheet has unusual formatting. A quick sanity check catches these problems before they reach a report.
5. If you're troubleshooting date-related zeros in SUMIFS, the guide on SUMIFS returning zero when date criteria use text-formatted cells covers a different but equally frustrating silent failure worth knowing about.