Fixing Excel SUMIFS That Returns Wrong Total When Sum Range Contains Merged Cells

You've written what looks like a perfectly correct SUMIFS formula, but the total is wrong — too low, or wildly off from what you'd get by adding the numbers manually. Everything else looks fine. The formula syntax checks out, the criteria match, and yet the result is short by exactly the values that sit in merged cells. That's the bug.

Merged cells are one of Excel's most common sources of silent formula errors. Unlike a syntax mistake, this one produces a result — just the wrong one — so it can slip past review unnoticed.

What you'll learn

• Why SUMIFS misreads merged cells in the sum range
• How to quickly confirm that merged cells are the root cause
• Four concrete fixes, from simplest to most robust
• How to avoid the same issue in future spreadsheets

What's actually going wrong

Consider a sales report where the Revenue column uses merged cells to visually group entries by region. Cells B2:B4 are merged and display 9000. To your eyes, each of those three rows contains 9000. To Excel, only B2 contains 9000 — B3 and B4 are empty.

When SUMIFS scans the sum range and hits a merged block, it reads the value from the top-left cell of that block and treats every other cell in the merge as blank. If your criteria match rows B3 or B4, their contribution to the total is zero, not 9000. The formula returns a number that's technically valid from Excel's perspective, but completely wrong from yours.

Why Excel only reads the top-left merged cell

This isn't a bug in SUMIFS specifically — it's how Excel's data model handles merging. When you merge a range, Excel stores the value in the anchor cell (top-left) and marks the remaining cells as occupied but valueless. Any formula that references those cells — SUM, SUMIFS, VLOOKUP, INDEX, you name it — receives an empty string or zero from the non-anchor cells.

This is exactly the same mechanism that causes XLOOKUP to return the wrong value when the search array has merged cells and why INDEX MATCH throws #N/A errors when a lookup range has merged cells. The root cause is identical: merged cells only hold one real value.

How to confirm merged cells are the culprit

Before you start changing anything, verify the diagnosis. Open the Find & Select tool to highlight every merged cell in your sheet.

1. Press Ctrl+H to open Find & Replace, then click Options > Format.
2. In the Format dialog, go to the Alignment tab and check Merge cells, then click OK.
3. Leave the Find what field blank and click Find All.

Excel will list every merged cell in the workbook. If your sum range appears in that list, you've confirmed the problem. You can also click a cell inside the suspected range and check the Home > Alignment group — the Merge & Center button will appear active/highlighted if that cell is part of a merge.

A faster sanity check: click a non-anchor cell in the merged block (say, B3 in our example) and look at the formula bar. If it's empty when the cell visually shows a value, you're looking at a merged cell where the value lives somewhere else.

Fix 1: Unmerge and fill cells with real values

This is the cleanest fix. Unmerge the cells and put the actual value in every row. That's what SUMIFS needs to function correctly.

1. Select the merged range in your sum column.
2. Go to Home > Merge & Center > Unmerge Cells.
3. With the range still selected, press Ctrl+G > Special > Blanks to select only the now-empty cells.
4. Type = followed by the address of the cell directly above (e.g., =B2), then press Ctrl+Enter to fill all blank cells at once.
5. Copy the entire column, then paste as Values only (Ctrl+Shift+V or Paste Special) to convert those fill-down formulas into static numbers.

After this, every row has its own numeric value and SUMIFS will total them correctly. This approach also makes sorting, filtering, and pivot tables work properly — merged cells break all of those too.

Fix 2: Use a helper column to replicate values

If you can't touch the original layout — maybe another team owns that sheet, or the merged formatting is a business requirement — add a helper column that extracts real values from the merge anchor.

Suppose your merged revenue values are in column B (B2:B100). Insert a new column C and enter this formula in C2:

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

Drag this down through C100. For every non-anchor cell (which appears blank), the formula pulls the value from the row above, effectively replicating the anchor value down through the merge. Then point your SUMIFS at column C instead of column B:

=SUMIFS(C2:C100, A2:A100, "North")

This approach leaves the original merged layout intact while giving your formula a clean, unmerged range to work with. Hide column C if you want to keep the sheet tidy.

Fix 3: Replace SUMIFS with a SUMPRODUCT workaround

There's a common misconception that SUMPRODUCT handles merged cells differently. It doesn't — it has the exact same limitation. If you point SUMPRODUCT at merged cells, it reads only the anchor values too. For a deeper look at that failure mode, see why SUMPRODUCT returns zero when criteria reference merged cells.

That said, you can use SUMPRODUCT together with a lookup that fills in the replicated values on the fly. If the merge structure is predictable (e.g., every 3 rows share a value), an INDEX-based approach can work. But for most real-world data, the helper column in Fix 2 is simpler and more maintainable than a complex array formula.

The real value of SUMPRODUCT is when your criteria need OR logic or array constants — scenarios covered in detail in the article on fixing SUMIFS that returns zero when criteria use array constants. For the merged-cell problem, Fix 1 or Fix 2 will serve you better.

Fix 4: Use structured tables to prevent merges

The long-term fix is removing the conditions that make merged cells tempting in the first place. Excel Tables (Insert > Table) give you banded rows, filter arrows, and automatic formula propagation — which means there's no visual reason to merge cells just to indicate grouping.

Instead of merging B2:B4 to show that three rows belong to the "North" region, keep "North" in every row. Use a pivot table or group-by aggregation if you need a summary view. This way, every cell in your data range holds its own value, and SUMIFS (along with every other formula) works exactly as expected.

If you need to present grouped data to stakeholders, format it as a pivot table or use Outline > Group to collapse rows visually — without merging any cells in the source data.

Common pitfalls to avoid

Assuming unmerge fixes everything automatically

When you unmerge, Excel empties all non-anchor cells. If you forget step 3–5 from Fix 1 (filling the blanks), your SUMIFS will still return a wrong total — this time because the cells are genuinely empty, not just merged. Always fill after unmerging.

Merged cells in criteria ranges cause a different bug

This article focuses on merging in the sum range. If your criteria range also has merged cells, you'll hit a different but related problem: the criteria match fires on the anchor row only, so entire groups of rows get excluded from the sum. The symptom looks similar but the fix targets the criteria column instead. Always check both ranges when diagnosing a wrong total.

The helper column formula order matters

In Fix 2, the formula =IF(B2<>"", B2, C1) must be entered starting from row 2 and dragged downward. If you start in the middle of the range, earlier rows haven't been filled yet and the chain breaks. Always build helper-column fill-downs from the top of the data.

Paste as values before deleting the helper column

If you use a helper column and later decide to delete the original merged column, your helper formula =IF(B2<>"", B2, C1) will break because it references the column you just deleted. Convert the helper column to static values first (Copy > Paste Special > Values), then delete the source column safely.

Text-formatted numbers in the sum range compound the problem

If your sum range contains numbers stored as text AND merged cells, you're dealing with two separate bugs at once. Fix the merge issue first, then address any text-number formatting. The article on fixing SUMIFS that returns zero when the sum range contains text-formatted numbers walks through the second part in detail.

Wrapping up

Merged cells in a SUMIFS sum range silently drop all values except the anchor cell, producing a total that's wrong with no error message to tip you off. Here's what to do next:

• Audit your sum range now: use Find & Select to locate every merged cell, and confirm whether any fall inside the range your formula references.
• Unmerge and fill if you control the source data — this is the cleanest, most compatible fix.
• Add a helper column if the merged layout must stay, and point your formula at the helper instead.
• Check both ranges: verify that neither the sum range nor the criteria ranges contain merged cells before calling the formula fixed.
• Switch to structured tables for new spreadsheets, and use pivot tables or grouping for visual presentation instead of merged cells in data ranges.