To fix an Excel SUMIF #SPILL! error, either clear the cells blocking the formula’s output or change the formula so it returns one result instead of an array. A spill usually appears when SUMIF receives several criteria at once in Microsoft 365; Excel then needs room to display one sum for each criterion. The steps below help you identify which case applies and repair the formula without overwriting other data.
What You’ll Need
- A copy of the workbook, especially if you plan to clear or move cells.
- Excel for Microsoft 365 on Windows. Excel for the web and current Excel for Mac also support dynamic-array results, although some ribbon labels or dialogs can look different.
- The formula cell and the rows or columns it may need to spill into. Check whether those cells contain data you need to keep.
Step-by-Step
-
Read the error and locate the formula. Click the cell showing #SPILL! and inspect the formula bar. A SUMIF that uses a single criterion cell normally returns one value. If its criteria argument refers to a range of criteria, Excel may return several values and try to spill them into adjacent cells. Note the formula and where its output is expected to go before changing anything.
-
Check whether the formula is meant to return one result or several. For example, suppose A2:A6 contains category names A, B, A, C, B, while B2:B6 contains 10, 15, 8, 12, 5. D2:D4 contains the criteria A, B, C. With those ranges, enter this formula in E2 if you want one result for each listed category:
With categories in A2:A6, amounts in B2:B6, criteria in D2:D4, and the formula in E2, use
=SUMIF(A2:A6,D2:D4,B2:B6).Excel should return 18, 20, and 12 in E2:E4: A totals 10 + 8 = 18, B totals 15 + 5 = 20, and C totals 12. This is an intended spill, not an incorrect calculation. The error means Excel cannot place one or more of those results in the required cells.
-
Look for anything occupying the spill area. In the example, inspect E3 and E4 as well as the formula cell E2. A value, another formula, a space, or a formula that displays an empty string can block the result. Formatting such as a fill color usually does not block a spill by itself. Select any obstructing cells and review their contents in the formula bar before clearing them. Move or save important information elsewhere; do not erase data just to make the error disappear.
-
Clear or relocate only the confirmed obstruction. If a cell in E2:E4 contains data that is no longer needed, select that cell and press Delete. On Windows, Delete clears the selected cell contents; on Mac, use the Delete key (which may be labelled Backspace on some keyboards). If you need to keep the content, move it to a different area or place the SUMIF formula where its full output range is empty. Once the path is clear, the results should spill automatically; you do not need to copy the array formula down.
-
Use one criterion if you only need one total. If you wanted the sum for the category in D2 only, point SUMIF to D2 rather than D2:D4. With the same category and amount data in A2:A6 and B2:B6, D2 holding the chosen category, and the formula entered in E2, use
=SUMIF($A$2:$A$6,D2,$B$2:$B$6). For D2 set to A, E2 returns 18. You can copy E2 down through E4 to calculate one result per criterion; each row then uses its own criterion cell. The dollar signs keep the data ranges fixed when you copy the formula. -
Check for merged cells and Excel tables. A merged cell anywhere in the intended spill area can prevent results from appearing. Select the affected area and choose Home > Merge & Center > Unmerge Cells if it is safe to do so. Dynamic-array formulas also cannot spill from inside an Excel table. If the SUMIF is in a table column, put a multi-criteria formula in an empty cell outside the table, or use a one-criterion formula in each table row.
-
Recalculate and confirm the result. After clearing the obstruction or adjusting the criteria reference, press Enter in the formula cell if you edited it. Check that the returned values line up with the criteria and that totals are sensible. If you changed a formula and still see an error, click the error cell again and read the updated message; Excel may now be identifying a different obstruction.
Common Mistakes
Assuming every SUMIF should produce only one value. A range supplied as the criteria argument can make a dynamic-array version of Excel calculate a separate SUMIF result for each criterion. The calculation is valid, but its output needs a clear spill range. Decide whether the goal is a list of totals or just one total before editing.
Deleting the formula or clearing a whole column. The formula may be correct, and unrelated cells may hold important records. Inspect the expected output cells first, then clear or move only the specific blocker. If you cannot safely free the spill area, move the formula instead.
Thinking formatting is always the cause. A colored or bordered cell is generally not enough to block a spill. Actual contents, merged cells, or a table boundary are more likely. Conversely, a cell that looks blank can still contain a space or a formula returning an empty string.
Making the ranges inconsistent. SUMIF’s sum range should correspond row by row with its criteria range. For example, if the category range is A2:A6, a sum range of B2:B5 leaves the ranges with different sizes and can produce confusing results. Use matching, correctly aligned ranges and verify that the first row in each range describes the same record.
Tips
Plan the output space before entering a formula that uses multiple criteria. Leave enough empty cells below or beside the formula for every requested result, and keep notes or source data outside that area. If criteria are likely to grow, reserve extra room or put the results in a separate worksheet so newly added criteria do not collide with existing content.
Use a single criterion reference when a report needs one total per row. That approach is easy to copy down and makes each result’s criterion visible beside it. Use a criteria range when you deliberately want a dynamic list of results that expands or contracts with the list. Avoid using an entire worksheet column as an array of criteria when you only need a short list; a very large potential output is difficult to manage and can run into worksheet limits.
When a spill error appears, make a note of the original formula before editing. If the warning indicator beside the cell offers an option to select obstructing cells, you can use it to help locate the blockage, then verify each selected cell before clearing anything. On Windows, the formula bar and the Home tab are on the ribbon interface; Excel for Mac has comparable formula and Home controls, but their placement can vary by release. Excel for the web provides similar spill behavior, though some diagnostic options may differ.
Troubleshooting
The spill area looks empty, but #SPILL! remains. Click each cell in the expected output range and inspect the formula bar. Look for a typed space, a formula returning an empty string, or content in a hidden row or column. Also check for merged cells. Clear only confirmed unwanted content, or move it and then recalculate.
The error says the spill range is in a table. Move the multi-criteria formula outside the table into a blank worksheet area. If each table row should show its own total, change the formula to use that row’s single criterion instead of a whole criteria list. A table is useful for structured data, but it does not provide an area for a dynamic array to expand inside the table.
The formula now returns a number, but it is not the expected number. Confirm that the criteria cell contains the intended text, that the criteria and sum ranges cover the same records, and that the amounts are numeric values. Check for extra spaces or category spellings that differ from the criteria. SUMIF adds values matching its criterion; it does not automatically correct inconsistent labels or convert every text-formatted amount.
The formula spills more results than expected. Review the criteria reference in the formula bar. A reference such as D2:D100 asks for a result for each cell in that range, even if some appear blank. Use the actual criteria list range, or switch to a single criterion cell and copy the formula row by row if that is the intended layout.
You cleared a blocker but the error did not change. The output may be blocked in another cell, by a merged area, or by a table. Select the formula cell and inspect the spill outline or error details again. If the result is meant to spill but the destination cannot be made available, place the formula in another blank area rather than repeatedly deleting cells.
Frequently Asked Questions
Why does SUMIF show #SPILL! when it usually returns one number?
In dynamic-array Excel, a range of criteria can produce one result per criterion. Excel displays #SPILL! when it cannot put all those results into the required adjacent cells. A single criterion normally produces a single result.
Can I stop the formula from spilling?
Yes. If you only need one total, use a single criteria cell rather than a criteria range. If you need a separate total on every row, use a single-criterion formula in the first row and fill it down. Keep a multi-criterion formula when you want its results to spill as a list.
Does #SPILL! mean that SUMIF calculated the wrong total?
Not necessarily. It often means the formula cannot display its full array of results. Once the spill area is available, check the totals against the source rows to confirm the calculation is also correct.
Can SUMIF spill inside an Excel table?
A dynamic-array result cannot spill within a table. Put the array formula in a blank area outside the table, or use a formula with one criterion in each table row when that layout suits the report.
Will this fix work in Excel for Mac or Excel for the web?
The core checks are the same in current versions that support dynamic arrays: inspect the criteria reference, clear or move blockers, and avoid putting a spilling formula inside a table. Ribbon locations and some error-help options may vary by platform.
Related
- Export an Excel Sheet to an Image: Methods and Tips
- How to Export an Excel Sheet to Word: Practical Methods
- How to Export an Excel Tab to a New Workbook
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Power Query Formula Firewall Error: Causes and FixesThe Excel Power Query Formula.Firewall error usually means a query is combining data sources or referencing another query…
- COUNTA Not Working in Excel? Fix Common Counting ErrorsIf COUNTA is not giving the result you expect in Excel, first check what is actually in the…
- Excel XLOOKUP Name Error? Causes and FixesAn Excel XLOOKUP #NAME? error usually means Excel does not recognize the XLOOKUP function or another name used…
- Excel Value Error: Why It Appears and How to Fix ItExcel’s #VALUE! error means a formula received a value or argument it cannot use in that calculation; it…
- Freeze VLOOKUP Formula in Excel: Lock or Fix ResultsTo keep a VLOOKUP’s lookup range from shifting when you copy the formula, make that range absolute by…
- Null Error in Excel: Causes and FixesIn Excel, the #NULL! error usually means a formula asks for the intersection of two cell ranges that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.