SUMIFS Spill Error: Causes and Fixes

A SUMIFS spill error usually means Excel is trying to return several results, but something prevents the results from filling the cells below or beside the formula. In Microsoft 365 for Windows, clear the intended spill area, remove merged cells, or move the formula outside an Excel table; if you only need one total, use a single criterion value instead of a range of criteria.

What a SUMIFS spill error means

SUMIFS adds numbers that meet one or more conditions. A conventional SUMIFS formula with one criterion normally returns a single value. In Microsoft 365, however, a criterion can be a range of values, and the formula can return one total for each criterion as a dynamic array. Excel displays those results in a spill range: the formula occupies one cell, while the other results appear in neighboring cells automatically.

For example, a formula that calculates totals for North, South, and West may need three output cells. If any cell Excel needs for those results is unavailable, Excel shows #SPILL! in the formula cell. The error is about placing the array results, not necessarily about the arithmetic in SUMIFS. A blocked spill range is the most common cause, but tables, merged cells, and formulas near the worksheet edge can also matter.

How SUMIFS can return multiple results

Suppose A2:A7 contains region names and B2:B7 contains sales amounts. The rows are North 120, South 80, North 50, West 100, South 40, and West 60. Put the three requested regions in D2:D4: North, South, and West. With those ranges and values in place, enter this formula in F2:

With regions in A2:A7, sales in B2:B7, and criteria in D2:D4, enter =SUMIFS(B2:B7,A2:A7,D2:D4) in F2.

Excel returns 170, 120, and 160 in F2:F4, respectively. The arithmetic is North 120 + 50 = 170, South 80 + 40 = 120, and West 100 + 60 = 160. The formula itself is in F2; F3 and F4 are part of its spill range and should not be typed into. If the criteria range contains more values, Excel needs more output cells.

By contrast, if D2 contains just North, this formula returns one result, 170, in the formula cell:

With regions in A2:A7, sales in B2:B7, and the single criterion in D2, enter =SUMIFS(B2:B7,A2:A7,D2).

That distinction answers a common misconception: SUMIFS does not always spill. It spills when its inputs produce an array of results in a dynamic-array version of Excel. A normal single-criterion formula remains a scalar calculation.

Find and remove a blocked spill range

Start by selecting the cell that displays #SPILL!. Excel may show a warning icon beside it; open the error menu and, where available, choose Select Obstructing Cells. This helps locate cells occupying the area the formula needs. The obstruction might be a value, a space, another formula, or a cell that looks empty but contains content.

To fix an ordinary obstruction, decide whether that cell’s content is needed. Move or delete it if appropriate, or move the SUMIFS formula to a location with enough empty space. Clearing a spill area is not the same as clearing the formula cell: keep the formula in its top-left cell, and leave the cells where results should appear available.

Use these checks when the obstruction is not obvious:

Select the cells below and to the right of the formula where results should appear, and look for values, spaces, or existing formulas.
Check whether a cell contains a formula that displays an empty string. It can look blank while still occupying a cell and blocking a spill.
Look for merged cells anywhere in the intended spill range. Unmerge them or move the formula to an unmerged area.
Confirm that the output has room before the worksheet’s last row or column. An array cannot spill beyond the worksheet boundary.

Do not delete obstructing data automatically if it may be important. Moving the formula is often safer. Once the obstruction is removed, Excel recalculates the array; there is no need to copy the formula down.

Excel tables and other special cases

Dynamic array formulas cannot spill from inside an Excel table. If the SUMIFS formula is in a table column and its criterion input is an array, the table’s calculated-column behavior conflicts with a multi-cell spill. Put the formula in a regular worksheet range outside the table, leaving enough clear cells for its results. The table can remain in place as the source data.

If the formula must be outside the table, a reference to table columns can keep the source ranges readable. For example, assume a table named SalesData has Region and Amount columns, and D2:D4 contains the criteria. Enter this formula in F2, outside the table:

With the source data in SalesData[Amount] and SalesData[Region], and criteria in D2:D4, enter =SUMIFS(SalesData[Amount],SalesData[Region],D2:D4) in F2.

The three totals spill from F2 if that output range is clear. Alternatively, if you do not need the table’s features, Excel for Windows provides Table Design > Convert to Range after selecting a table cell. This changes the table into an ordinary range; it is not necessary just to fix the formula, and structured references may change when the table is converted.

Other errors can look related but have different causes. A wrong range or mismatched range dimensions may produce an incorrect result or another error rather than #SPILL!. Check that the sum range and criteria ranges cover corresponding rows, and that each criterion is the type and value you intend. A spill error specifically points to an inability to place an array result.

Choose between a spilling formula and copied formulas

A single dynamic-array formula is convenient when a list of criteria should produce a matching list of totals. It is easy to update the criteria list, and the results resize as the list changes. It also requires a clear output area and a version of Excel that supports dynamic arrays, such as Microsoft 365.

For a report that needs one formula per row, enter a scalar SUMIFS formula beside the first criterion and fill it down. For example, assume the same data layout as before, but put criteria in D2:D4 and place this formula in F2:

With regions in A2:A7, sales in B2:B7, and the row’s criterion in D2, enter =SUMIFS(B$2:B$7,A$2:A$7,D2) in F2, then copy it into F3:F4.

The formulas return 170, 120, and 160, with each row referring to its own criterion. The dollar signs keep the source ranges fixed while the criterion reference changes by row. This approach is useful when each result needs separate formatting, notes, or manual review, and it avoids relying on a spill range.

In Microsoft 365 for Windows, a spill result is often the simpler choice for a compact summary. Copied formulas can be a better fit for older workbook designs, for recipients using versions without dynamic arrays, or when the output cells must be individually editable. Do not type into cells that belong to an active spill range; change the source criteria or formula instead.

Version and platform considerations

Dynamic arrays are available in Microsoft 365, including Excel for Windows. In Excel for the web, dynamic arrays are also supported, although some interface details can differ from the desktop app. If a workbook is opened in an older Excel version that does not support the relevant dynamic-array behavior, the formula may not calculate or display as expected. For a workbook shared across mixed versions, a row-by-row formula can be more compatible.

On Windows, select the formula cell and inspect Excel’s spill-error warning to locate an obstruction. On Mac, the same general diagnosis applies: check the cells the array needs, merged cells, and whether the formula is inside a table. Ribbon placement and the error menu can differ slightly by Excel version and platform, so use the warning shown with the selected cell rather than relying on a Windows-only menu location. Excel for the web also provides cell error details, but its controls may be arranged differently.

Practical troubleshooting sequence

Work through the problem in an order that distinguishes a blocked output from a formula-design issue:

Check whether the formula uses a range of criteria, such as D2:D4, when you expected just one criterion. If only one total is wanted, refer to one cell, such as D2.
Estimate how many results Excel needs to return. Three criteria values ordinarily call for three outputs.
Inspect that output area for values, formulas, spaces, merged cells, or table placement.
Clear or move any genuine obstruction, or relocate the formula to a larger empty area.
Verify the sum and criteria ranges cover the same records and that the criteria cells contain the intended labels.
Recheck the result against a small manual calculation, especially if criteria may contain extra spaces or inconsistent spelling.

If Excel still shows an error after the output area is clear, read the specific error message rather than assuming every SUMIFS problem is a spill problem. A misspelled function, an invalid reference, or inconsistent source data needs a different correction. The key diagnostic question is whether the formula is asking Excel to return more than one result.

Frequently Asked Questions

Why does SUMIFS show #SPILL! when the cells look empty?
One or more cells in the required output range may contain a space, a formula returning an empty string, or another value that is not obvious at a glance. Select the error cell and use its warning menu to identify obstructing cells when that option is available. Also check for merged cells and table placement.

Does SUMIFS always spill in Microsoft 365?
No. A formula with a single criterion normally returns one total. A formula that uses an array of criteria can return multiple totals, which Excel places in a spill range. Whether it spills depends on the formula’s inputs and context.

Can I put a spilling SUMIFS formula inside an Excel table?
No. A dynamic array formula cannot spill from within a table. Put the formula in a normal worksheet range outside the table, with clear cells available for its output, or use a separate scalar formula in each table row.

Can I fix #SPILL! by copying the formula down?
Usually, that is not the right fix for a formula intended to return an array. Copying it down can create duplicate calculations or occupy cells the original formula needs. Either clear the intended spill area or change the formula to use one criterion per row and then fill it down deliberately.

Is #SPILL! the same as a wrong SUMIFS total?
No. A wrong total is a calculation or data issue; #SPILL! means Excel cannot place the array result in the worksheet. After fixing the spill, check the criteria and ranges separately to confirm that the returned totals are correct.

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.