SUMIFS Returns 0 for Stock Out: Fix Merged Cells

The SUMIFS formula returns 0 for Stock Out because the item name is stored in merged cells. Excel keeps the value only in the top-left cell of a merged area. The rows below appear to show the same animal or item, but their cells are actually blank, so the formula cannot match them.

This problem commonly affects small-business workbooks used for inventory movements, where Stock In and Stock Out lines are grouped visually instead of stored as complete records.

The problem

This issue came up in the r/excel community discussion about SUMIFS returning 0 for Stock Out.

A SUMIFS formula performs a conditional sum. It checks one or more criteria ranges, finds rows that match the conditions, and adds the corresponding values from the sum range. Microsoft defines the structure as:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

The sum_range is the quantity column. Each criteria_range contains a field such as the item name, movement type, warehouse, or date.

In the yellow table, the first Stock In line works because it is the top-left cell of the merged item-name area. That cell contains the animal or item name. The following Stock Out lines look as though they contain the same name, but the underlying cells are blank.

For example, the sheet may look like this:

Item name	Movement	Quantity
Dog	Stock In	10
Dog	Stock Out	3
Dog	Stock Out	2

If the item-name cells are merged, Excel actually sees something closer to this:

Item name	Movement	Quantity
Dog	Stock In	10
blank	Stock Out	3
blank	Stock Out	2

The SUMIFS formula can find the first row because Dog exists there. It cannot find the Stock Out rows when it requires both Dog and Stock Out. No matching rows means the formula returns 0.

The issue is not that SUMIFS treats Stock Out differently. The condition exposes the missing item names in those rows.

The solution

The robust fix is to unmerge the item-name cells and repeat the item name on every transaction row. Do not use merged cells as a data-storage method. They are useful for presentation, but they make filtering, sorting, formulas, PivotTables, and automation unreliable.

After restructuring the data, the formula can use complete records:

=SUMIFS($C$2:$C$100,$A$2:$A$100,E2,$B$2:$B$100,"Stock Out")

Here is what each part means:

• $C$2:$C$100 is the quantity or sum range.
• $A$2:$A$100 is the item-name criteria range.
• E2 contains the item name to find.
• $B$2:$B$100 is the movement-type criteria range.
• "Stock Out" is the condition that must match.

For Stock In, change only the final criterion:

=SUMIFS($C$2:$C$100,$A$2:$A$100,E2,$B$2:$B$100,"Stock In")

The formula is correct only when the item name, movement type, and quantity are present in the same row. Microsoft also recommends that the criteria ranges and sum range use matching dimensions.

An alternative is to fill the blank item-name cells with the value from the row above, but this is still a manual repair. A flat table with one complete record per row is more dependable.

How to apply it

1. Make a copy of the workbook before changing the layout.

2. Select the merged item-name column or the relevant merged areas.

3. Use Excel’s Unmerge Cells command.

4. Identify the blank cells created below each original item name.

5. Fill each blank item-name cell with the item or animal from the row above. Every Stock In and Stock Out line must have its own item name.

6. Check the movement column. Use one consistent spelling for each condition, such as Stock In and Stock Out. Avoid extra spaces and inconsistent capitalization.

7. Check the quantity column. Quantities should be numeric values, not numbers stored as text.

8. Convert the range into an Excel Table with Insert, Table. Tables expand automatically as new inventory movements are added.

9. Rebuild the formula using the table columns. If the table is named Inventory, a readable version is:

   =SUMIFS(Inventory[Quantity],Inventory[Item],E2,Inventory[Movement],"Stock Out")

   Inventory[Quantity] is the column to add. Inventory[Item] identifies the animal or product. Inventory[Movement] contains Stock In or Stock Out. E2 is the selected item.

10. Test the result with one item that has both movement types. Add the Stock In quantities separately, then add the Stock Out quantities. Confirm that each total matches the source transactions.

11. If the result is still 0, inspect the criteria text. A visible Stock Out value may contain a trailing space. You can test a cell with:

    =LEN(B2)

    Compare the character count with a clean Stock Out label. The LEN function counts characters, including spaces.

12. Use filters to confirm that every Stock Out row has an item name. Any blank item cell indicates that the data structure still needs correction.

The key principle is simple: formatting should not replace data. Merged cells hide blank records, while formulas need explicit values in every row.

Implementation angle

This inventory layout is a good candidate for a structured data-entry process in Zoho Creator. Each movement can become a record with fields for item, movement type, quantity, date, and location, while Make or n8n can synchronize approved transactions back to Excel or a reporting system. An automation partner can also use Claude to inspect formulas and flag merged cells, blank item names, inconsistent Stock Out labels, and quantities stored as text before they affect reports.

Book an AI implementation engagement with Creator Scripts at creatorscripts.com.

Frequently asked questions

Why does SUMIFS return 0 for Stock Out in Excel?

The item name may be in a merged cell, so the Stock Out rows are blank in the underlying data. SUMIFS cannot match those rows to the item name. Unmerge the cells and enter the item name in every transaction row.

How do I sum Stock Out quantities by item in Excel?

Use SUMIFS with the quantity column as the sum range, then set criteria for the item and movement type. For example: =SUMIFS($C$2:$C$100,$A$2:$A$100,E2,$B$2:$B$100,"Stock Out"). Make sure every range covers the same rows and each Stock Out row has an item name.

What should I check if my SUMIFS formula still returns 0?

Check that the item name and movement type match the criteria exactly, and that quantities are stored as numbers. Look for blank item cells and extra spaces in labels such as “Stock Out.” A flat table with one complete transaction per row is easier to check and summarize.