Excel SUMIF Spill Error: Why It Happens and How to Fix It

Errors & fixes · Updated November 19, 2026

A SUMIF spill error (#SPILL!) means Excel is trying to return multiple results but cannot put them into the cells it needs. Check whether the criteria argument is a range or array; if you want one total, use a single-cell criterion, or clear the cells where the results should spill.

What a SUMIF spill error means

SUMIF adds values that meet a condition. Its syntax is =SUMIF(range, criteria, [sum_range]): range contains the values Excel tests, criteria specifies what to match, and the optional sum_range contains the numbers to add. In Microsoft 365, a formula can return more than one result when its criteria argument contains multiple criteria. Excel then needs to place those results in neighboring cells, a behavior called spilling.

For example, one criterion such as Tea usually produces one total. A criteria range containing Tea, Coffee, and Cocoa can produce three totals. The formula is not necessarily wrong when #SPILL! appears: the intended output may simply have nowhere to go. If the formula uses just one criterion cell and still shows the error, inspect the formula for an array or range reference that makes Excel expect multiple outputs.

#SPILL! is different from #VALUE!, #NAME?, and a total that is merely incorrect. It describes a problem placing an array result, not a complaint that SUMIF cannot recognize its function name or perform the addition.

How criteria ranges create multiple results

Consider a small worksheet with product categories in A2:A7 and sales amounts in B2:B7. The rows contain Tea, Coffee, Tea, Cocoa, Coffee, and Tea; their amounts are 12, 20, 8, 15, 10, and 5. Put the criteria Tea, Coffee, and Cocoa in D2:D4. With that layout, enter this in F2:

=SUMIF(A2:A7,D2:D4,B2:B7)

Excel attempts to return three results in F2:F4: 25 for Tea, 30 for Coffee, and 15 for Cocoa. The values are calculated by adding the matching amounts in column B. If F3 or F4 contains something, the results cannot occupy the full spill range and F2 displays #SPILL!. The dotted outline around the intended output area can help show which cells are involved.

If the goal is only Tea’s total, put Tea in D2 and use the single-cell criterion instead. With the same category and sales data, and Tea in D2, enter this in F2:

=SUMIF(A2:A7,D2,B2:B7)

This returns 25 and does not need a multi-cell spill area. A useful diagnostic is to look closely at the second argument: D2 is one criterion; D2:D4 asks Excel to calculate results for several criteria.

Find and remove whatever blocks the spill

When multiple results are intended, keep the formula and make the expected output area available. Start by selecting the #SPILL! cell and checking Excel’s error indicator; where offered, Select Obstructing Cells can take you to a blocking cell. You can also inspect the cells within the dotted spill outline.

  • Delete or move existing values in the spill area. A space, an apostrophe, or a formula that displays an empty string can still mean a cell is occupied.
  • Check for merged cells within the destination area. Unmerge them if appropriate, or move the formula so the results do not need to cover them.
  • Look for another formula or data in a cell that the results need. Move that content rather than deleting it if it is important.
  • Confirm there is enough room below or beside the formula for every result. A result that would run beyond the worksheet edge cannot spill.

Formatting alone, such as a fill color or number format, generally does not block a spill. Hidden rows are not a substitute for empty cells either: hidden cells can still be part of the output range. Before clearing anything, check that the contents are not needed elsewhere.

When the formula is inside an Excel table

Dynamic array formulas cannot spill from within an Excel table. If a SUMIF formula inside a table refers to several criteria and returns an array, it may show #SPILL! even when nearby worksheet cells look empty. Move the multi-result formula outside the table, or change the calculation so each table row returns one result.

For a row-by-row table calculation, use a single criterion from the current row. Suppose a table is named Sales and has columns Category and Amount. Enter this formula in a normal calculated column within that table:

=SUMIF(Sales[Category],[@Category],Sales[Amount])

Each row uses its own Category value and returns one total for that category. If the table includes repeated categories, the same category total will appear on each matching row; that is different from creating one spilled list of totals. If you need a separate summary list, put its formula in the worksheet outside the table and leave enough cells free for the results.

Choose between one total and several totals

First decide what result the report needs. A single SUMIF is appropriate when you want one total for one criterion. A SUMIF with a criteria range is useful when you want a corresponding total for every listed criterion and have open worksheet cells for the results.

Sometimes you want one combined number for several criteria, not a spilled list. With the category and sales data above, and Tea in D2 and Coffee in D3, use this formula in F2:

=SUM(SUMIF(A2:A7,D2:D3,B2:B7))

The inner SUMIF calculates 25 and 30; the outer SUM adds them, returning 55 in one cell. This avoids needing a spill range. Be aware that repeated criteria are counted repeatedly: if Tea appears twice in D2:D3, Tea’s total is included twice. Remove duplicates from the criteria list if that is not what you intend.

For two or more conditions that must all be true for each row—for example, a particular category and a particular region—SUMIFS is generally the relevant function. That is a different task from supplying a list of alternative criteria to SUMIF. Choosing the function based on whether conditions are alternatives or simultaneous requirements helps prevent both incorrect totals and unexpected arrays.

Check ranges, criteria, and the result you expect

A range mismatch is a common source of confusing totals, though it does not normally cause #SPILL! by itself. Keep the test range and sum range aligned in size and position. In the example, A2:A7 and B2:B7 both cover six rows. If a copied formula accidentally refers to A2:A7 but B3:B8, the function may still calculate, but it is testing one set of rows and adding values from another.

Also check whether a criterion is text, a number, a date, or a comparison such as greater than a value. If the criterion is stored in a cell, use that cell reference and verify its contents. For a condition written directly into a formula, quote text and comparison expressions correctly. With category names in A2:A7 and amounts in B2:B7, this formula adds amounts for categories beginning with Tea:

=SUMIF(A2:A7,"Tea*",B2:B7)

The asterisk is a wildcard for any following characters. This formula has one text criterion and returns one total; it does not ask for a result per criterion. Wildcards and criteria syntax affect what matches, whereas a spill error concerns where array results can be placed.

A practical troubleshooting sequence

Work through these checks in order so you do not change a correct calculation just to hide its error:

  1. Click the formula cell and identify which argument supplies the criteria. If it refers to several cells, decide whether several results are intentional.
  2. Read the spill outline or inspect Excel’s obstruction warning to identify the required output cells.
  3. If you want several totals, clear or relocate the obstructing contents, unmerge any cells in the output area, and ensure the formula is not inside a table.
  4. If you want one total, replace the criteria range with the one criterion cell you intend to use.
  5. Check that the criteria and sum ranges refer to the intended corresponding rows, then confirm the returned amount against the source data.

Do not add @ or wrap the formula in an unrelated function just to make the warning disappear without checking the desired result. Such changes can force a single value or alter the calculation while leaving the underlying question—one total or multiple totals—unanswered.

Frequently Asked Questions

Why does SUMIF show #SPILL! when the cells look blank?
One or more cells in the intended spill range may contain a space, an invisible entry, or a formula that displays an empty string. The formula may also be inside a table or the spill area may include merged cells. Check the full dotted outline rather than only the cell immediately below the formula.

Can SUMIF return more than one result?
Yes. In current Microsoft 365 Excel, providing multiple criteria through an array or range can return a corresponding array of totals. Excel needs an open destination range for those results. Use a single criterion cell when you need just one total.

How do I add several SUMIF results into one number?
Wrap the SUMIF that uses a criteria range in SUM, as in =SUM(SUMIF(A2:A7,D2:D3,B2:B7)) for the example layout above. With Tea and Coffee in D2:D3, it returns 55. Check for repeated criteria, because each occurrence contributes its total again.

Does formatting cause a SUMIF spill error?
Ordinary cell formatting does not occupy a cell and generally does not block a spill. Cell contents, merged cells, and table placement are more relevant checks. A cell that looks empty can still contain a space or a formula.

Will SUMIFS fix a SUMIF #SPILL! error?
Not automatically. SUMIFS is designed to sum values that meet multiple simultaneous conditions; it is not simply a spill-error repair. If the problem is a blocked output area or a multi-cell criteria array, choose whether you need a list of totals or one total, then adjust the formula and layout accordingly.

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.