excelavon

SUMIF Not Working With Merged Cells? Causes and Fixes

Errors & fixes · Updated November 3, 2026

SUMIF Not Working With Merged Cells? Causes and Fixes

Yes—merged cells can make SUMIF appear not to work because Excel stores a merged area’s value only in its upper-left cell. If a category is merged down several rows, SUMIF sees that category on the first row only; unmerge the cells and repeat the category on every relevant row so each value can match.

Why merged cells affect SUMIF

SUMIF evaluates the cells in its criteria range one by one and sums values from the corresponding positions in its sum range. A merged area may look like one label spanning multiple rows, but it is not a repeated label behind the scenes. The value is stored in the top-left cell; the other cells covered by the merge are empty.

That difference matters when each row represents a separate transaction or item. The visual layout may suggest that every row belongs to a category, while the worksheet data says only the first row does. SUMIF follows the underlying cell values, not the way the merge makes them look.

This is not usually a broken formula or an Excel calculation error. It is a mismatch between a presentation-oriented layout and the row-by-row structure that a conditional sum needs. Merged cells can still be useful for headings, but they are usually a poor fit for categories inside a list of records.

A simple example of the missing total

Suppose A2:A4 appears as one vertically merged cell containing East, while B2:B4 contains sales of 100, 150, and 200. With criteria in A2:A10 and sales in B2:B10, the formula =SUMIF(A2:A10,"East",B2:B10) returns 100. It finds East in A2 and sums the matching value in B2. A3 and A4 are blank as far as the formula is concerned, so the sales of 150 and 200 are not included. The intended total is 450.

The formula can be valid, calculate without an error, and still return a total that is too small. That is why checking the underlying rows is more useful than immediately rewriting the formula. If the category is repeated in A2, A3, and A4, the same formula includes all three sales values.

How to check whether a merge is the cause

Start by inspecting the criteria range, the cells SUMIF checks for the requested category. Select a suspicious cell and look at the Home tab: if the Merge & Center control indicates a merged selection, or the cell is part of a merged area, check which row actually contains the value. You can also click individual rows in the formula bar; a row that looks covered by a label may be blank.

Then compare the formula’s ranges. The criteria range and sum range should cover corresponding rows in the same order. For example, A2:A10 should normally be paired with B2:B10. If one range starts on row 2 and the other on row 3, Excel may sum values from the wrong positions. Merged cells can hide that kind of alignment problem because the displayed label spans several rows.

  • Check whether the label exists in every row that should qualify.
  • Confirm that the formula’s criteria range and sum range cover the intended rows.
  • Look for blank rows, misspelled labels, extra spaces, or numbers stored as text.
  • If the formula should update when data changes, check Formulas > Calculation Options > Automatic.

Fix the source data by unmerging and filling down

For a list that will be summed, filtered, sorted, or analyzed, the most reliable repair is to unmerge the category cells and put a category value on every record row. In Excel for Microsoft 365 on Windows, select the merged range, then choose Home > Merge & Center > Unmerge Cells. On Mac, select the range and use the Merge & Center control on the Home tab to unmerge it; ribbon layout can vary slightly by window size.

Unmerging does not copy the visible label into every newly separate cell. The original value remains in the top-left cell, and the other cells become blank. Fill those blanks with the appropriate category before relying on the total. For a short list, enter the category in each row. For a longer list, copy the category into the appropriate blank cells carefully, checking that group boundaries are preserved.

Once the category is repeated, the SUMIF formula can use the same aligned ranges. With categories in A2:A10 and sales in B2:B10, use =SUMIF(A2:A10,"East",B2:B10). If East occurs in rows 2 through 4 with values 100, 150, and 200, the result is 450. Verify the total against those rows before applying the approach to a larger sheet.

Use a helper column when the display must stay merged

Sometimes a worksheet is designed as a printed report and its merged appearance must remain. In that case, keep the presentation sheet separate from the data used for calculations. A helper column can hold a repeated category on every record row, while the merged label remains in the display area. Point SUMIF at the helper column rather than the merged cells.

For example, if the helper categories are in C2:C10 and sales remain in B2:B10, the formula =SUMIF(C2:C10,"East",B2:B10) sums sales for rows whose helper value is East. With the three example sales of 100, 150, and 200 correctly labeled East in C2:C4, the result is 450. Keep the helper range and sum range aligned row by row, and avoid leaving a helper cell blank when its record belongs to a category.

This method separates two different jobs: the report can be arranged for readability, while the helper data stays regular enough for formulas and analysis. If the worksheet is used by other people, label the helper column clearly or place the source data on a separate sheet so it is not mistaken for a duplicate field.

What if the merged cells are in the sum range?

Merged cells can also cause confusion when they contain the numbers to be added. In a merged area, only the upper-left cell holds a value. Other cells in the area do not contain repeated copies of that number. SUMIF cannot infer that the displayed number should be assigned to each row in the merge, and adding it once or expecting it to appear on every row can produce the wrong interpretation of the data.

Decide what the number represents before changing the sheet. If it is one total covering several records, it should generally be stored once in a summary area, not treated as a separate amount on each detail row. If it is an amount for each row, enter the amount in each appropriate row and unmerge the cells. Repeating a merged value without understanding its meaning can inflate a total, just as blank criteria cells can make a total too small.

Other SUMIF problems that can look similar

If unmerging and filling down does not fix the result, check for other common causes. Text criteria are generally not case-sensitive, but spelling still matters. A category such as East followed by an extra space is not necessarily the same text as East. Wildcards such as an asterisk can intentionally match partial text, but they can also make a criterion broader than expected.

For numeric criteria, confirm that the values are really numbers and not text that only looks numeric. Check that the sum range contains the expected amounts and that neither range has accidentally excluded the last rows. A formula returning zero may mean no criteria cells matched; a result that is too low may mean some matching rows were omitted. If the formula is correct but appears not to change after edits, check that calculation is set to Automatic or recalculate the workbook.

Also note that SUMIF is not case-sensitive. If two categories differ only by capitalization, SUMIF treats them as the same criterion. If that distinction matters, ordinary SUMIF is not the right tool by itself. For most merged-cell problems, however, the key issue is missing values in the criteria rows, not capitalization or a special formula feature.

Prevent the problem in future worksheets

Keep raw data in a rectangular list: one header row, one record per row, and one field per column. Repeat category values rather than relying on merged labels to imply that several records share a value. This structure makes SUMIF, filters, sorting, and PivotTables more predictable. Excel tables also require an unmerged, regular data layout, so avoiding merges makes it easier to turn a range into a table later.

Use formatting to make headings stand out without merging cells inside the records. For a heading across columns, Center Across Selection can provide a centered appearance without combining the cells, though it is a cell-formatting option rather than a way to label multiple data rows. Reserve merged cells for titles or report headings that are not part of the source data used by formulas.

Frequently Asked Questions

Does SUMIF ignore merged cells?
It evaluates the actual cell values. Because only the upper-left cell of a merged area contains the value, rows covered by the merge are blank to SUMIF and do not match that criterion.

Why does SUMIF return only the first row’s amount?
If a category is merged vertically, only its first row contains the category value. SUMIF matches that row and sums its corresponding amount, but it does not assume the blank rows below belong to the same category.

Will unmerging automatically fill the category into every row?
No. Unmerging leaves the original value in the upper-left cell and makes the other cells separate, blank cells. Fill the intended category into each relevant row before recalculating.

Can I keep the merged cells and still use SUMIF?
Yes, if SUMIF uses a separate helper column that repeats the category for each record. Another option is to keep a clean data sheet and use a separate formatted report for merged headings.

Should I replace SUMIF with SUMIFS?
Not just to solve a merge problem. SUMIFS supports multiple criteria, but it still needs matching criteria values in the relevant rows. Repair the data structure first; choose SUMIFS only when you need to apply more than one condition.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.