Excel’s COUNTIF usually returns one count when its criteria argument is a single value. In Microsoft 365, it can return several counts—and show #SPILL!—when the criteria argument contains multiple values and Excel cannot place all the results in the cells below or beside the formula. Make room for the spill range, move the formula outside a table, or use a single criterion if you only need one count.
What a COUNTIF spill error means
A spill error is Excel’s way of saying a formula wants to return multiple results, but it cannot put them all in the worksheet. The formula lives in an anchor cell, usually the first cell of the intended results. Excel tries to fill the surrounding spill range automatically. If part of that range is unavailable, the anchor displays #SPILL!.
That is different from a normal COUNTIF formula with one criterion. For example, asking how many cells in a list contain the value in one cell produces a single answer. But if the criteria argument refers to a range of several search values, Excel for Microsoft 365 can calculate a separate count for each value and return an array. The array needs space to spill.
So, the error does not necessarily mean COUNTIF is written incorrectly or that the source data is bad. Often the formula is valid, but the destination cells prevent its results from appearing. A single-criterion COUNTIF that shows this error may also be part of a larger array formula or may not be the formula currently in the cell; inspect the formula bar before changing it.
How COUNTIF can return multiple results
COUNTIF has two arguments: the range to examine and the criterion to count. With a single criterion, it returns one number. With an array of criteria, current Excel can calculate one number for each criterion. This is useful when you want a compact summary without writing a separate formula for every search value.
Suppose A2:A8 contains Apple, Pear, Apple, Banana, Pear, Apple, and Banana, in that order. D2:D4 contains Apple, Pear, and Kiwi. With those cells as the source list and criteria list, enter this formula in F2:
With categories in A2:A8, criteria in D2:D4, and an empty output area beginning at F2, use =COUNTIF($A$2:$A$8,D2:D4). The results are 3, 2, and 0 in F2:F4: three Apples, two Pears, and no Kiwis.
That formula is not asking COUNTIF for one answer. It asks for a set of answers corresponding to the three criteria cells. The results need to spill down from F2. If a cell in F3 or F4 already contains a value, Excel cannot show the full result and the formula cell may display #SPILL!. This array behavior is a feature of dynamic-array Excel, not a special COUNTIF error mode.
Find what is blocking the spill range
Start with the cell showing #SPILL!. Select it and inspect the dashed outline that indicates where Excel wants to place the results. The outline can help you see which cells are included in the intended spill range. Select the warning or error icon if it appears; Excel may identify the specific obstruction, such as a nonblank cell or a merged cell.
Check every cell in the outlined area, not just the first one. A cell can look empty while containing a space, an apostrophe, or a formula that returns an empty string. Such a cell is still occupied for spill purposes. Clear the contents of cells that should hold the output, or move the formula to a genuinely clear area. Clearing contents is different from deleting cells, which can shift other worksheet data.
Also check for merged cells in the intended output area. A dynamic array cannot spill into merged cells. Unmerge the relevant cells if appropriate, or put the formula in a location where the entire output range is unmerged. Formatting alone—such as a fill color or border—does not usually block a spill; cell contents and structural limitations are the first things to investigate.
- Select the formula cell and identify the outlined spill range.
- Look for values, spaces, formulas, or merged cells within that range.
- Clear or relocate the obstruction, or move the formula to a clear area.
- Check whether the spill range would extend beyond the worksheet or into a table.
Common causes and practical fixes
A filled cell in the destination is the most straightforward cause. If the formula is in F2 and needs F2:F4, even an unrelated note in F3 can prevent the full array from appearing. Move the note, choose another output column, or clear the cell if its content is no longer needed. Do not overwrite data until you have confirmed what it is.
Excel tables are another frequent source of confusion. Dynamic array formulas do not spill within an Excel Table. If the COUNTIF formula is inside a table and its criteria produce an array, move the array formula to a normal worksheet range outside the table. Alternatively, use a separate row-by-row formula in a table column so each table row calculates one scalar result.
With a category in each row of a table named Inventory, and the category values in its Category column, enter this formula in the table’s count column: =COUNTIF(Inventory[Category],[@Category]). It counts how many times the current row’s category appears in the table. Each row has one criterion, so it returns one number per row rather than relying on one formula to spill multiple values. Replace the example table and column names with the names in your workbook.
A very large criteria array can also request more results than the worksheet can accommodate. For example, using an entire column as the criteria array may demand an impractically large result. Use a bounded range that covers the actual criteria, such as D2:D100, instead of referencing a whole column unnecessarily. If the intended spill range is blocked by merged cells or worksheet edges, changing the output location or reducing the criteria range may solve the problem.
Choose between one count and a spilled summary
If you need a count for just one search value, make the criterion a single cell reference. This produces one result and is often the simplest repair when an unintended array was caused by selecting a range of criteria.
With categories in A2:A8, the single criterion in D2, and the formula entered in F2, use =COUNTIF($A$2:$A$8,D2). For the example where D2 contains Apple, the result is 3. You can copy the formula down to calculate counts for other criteria one row at a time; the dollar signs keep the source range fixed while D2 adjusts to D3, D4, and so on.
Choose a spilled formula when you want one compact formula to calculate counts for a list of criteria and you can reserve enough empty cells for its results. Choose separate formulas when each result needs to sit in a table row, when you want individual formulas that are easy to inspect, or when the output area cannot be kept clear. Neither approach changes what COUNTIF counts; the difference is how the criteria and results are arranged.
Check criteria and other COUNTIF issues
COUNTIF is not case-sensitive: Apple and APPLE are treated as the same text criterion. It also recognizes wildcards in criteria: an asterisk represents any sequence of characters, and a question mark represents one character. A tilde can be used to treat a wildcard character literally. These rules affect which cells are counted, but they do not by themselves create a spill error. A spill occurs when the formula returns multiple results and the output cannot be placed.
Make sure the criteria cells contain what you expect. A cell that looks blank may contain a space, and numbers stored as text can behave differently from numeric values in some comparisons. If a count is wrong but there is no #SPILL!, check the data types, criteria, and source range separately. For example, confirm that the source range includes all intended rows and does not accidentally include a heading.
Do not confuse #SPILL! with other errors. #VALUE! or an unexpected count may point to different formula or data issues; a spill error specifically concerns returning an array to worksheet cells. Also, formatting cells as blank does not clear their contents. If you need to remove an obstruction, select the cell and use Delete or Home > Clear > Clear Contents, taking care not to erase information you still need.
Use the spill results elsewhere
When a dynamic COUNTIF array spills successfully, only the anchor cell contains the formula. The other visible results are part of its spill range, not separate formulas to edit independently. To refer to the complete result later, Excel’s spill-range operator can be useful. For example, if the formula is in F2, a reference to F2# refers to its full spill range and adjusts as the array’s size changes. This is a reference, not a fix for a blocked spill; the original formula still needs room to return its results.
Keep source and output ranges separate where practical. If the output overlaps the cells used as criteria or source data, edits can make the formula harder to understand and may cause a circular reference in some designs. A clear layout—with source data in one area, criteria in another, and results in a reserved output area—makes it easier to diagnose future errors and verify that each count is based on the intended cells.
Frequently Asked Questions
Does COUNTIF always spill in Excel 365?
No. With one criterion, COUNTIF normally returns one count. It can return multiple results when the criterion is an array or range of criteria, and those results spill only when the worksheet can accommodate them.
Why does COUNTIF show #SPILL! when the cells look empty?
A destination cell may contain a space, a formula returning an empty string, or another invisible value. Select the error cell and inspect its outlined spill range; clear genuine obstructions only after checking their contents.
Can COUNTIF spill inside an Excel Table?
Dynamic array formulas cannot spill inside tables. Put a formula that returns an array in a normal range outside the table, or use a one-criterion formula in each table row.
How do I stop COUNTIF from spilling?
Use one criterion cell rather than a range of criteria. With the source range in A2:A8, a single criterion in D2, and the formula in F2, =COUNTIF($A$2:$A$8,D2) returns one count. Copy it down if you need separate counts for more criteria.
Is #SPILL! the same as an incorrect count?
No. #SPILL! means Excel cannot place the formula’s array results in the worksheet. An incorrect numeric count usually calls for checking the range, criterion, data, or wildcard behavior instead.
Bottom line
An Excel COUNTIF spill error usually means the formula is returning more than one count but the intended output range is blocked, merged, inside a table, or too large for the available worksheet space. Inspect the spill outline, clear or avoid the obstruction, and use a single criterion if you only want one result. Once the formula’s intended output shape is clear, the right fix is usually straightforward.
Related
- Combine Multiple XML Files Into One Excel Workbook
- Combine Excel Files: Practical Methods and Steps
- What Column Number Is V in Excel? Find It in Seconds
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- IRR Formula Not Working in Excel? Causes and FixesThe IRR formula usually fails because the cash-flow range is incomplete or contains no valid positive-and-negative combination, the…
- Excel Merged Cells Must Be the Same Size? Fix the ErrorYes—when Excel says “To do this, all the merged cells need to be the same size,” it usually…
- Excel FILTER Function Errors: How to Diagnose and Fix ThemMost Excel FILTER errors come from a criteria range that does not match the data range, no rows…
- Excel Not Enough Memory Error: Causes and Fixes ExplainedIf Excel says there is not enough memory to complete an action, first save your work if possible,…
- Excel TRANSLATE Formula Not Working? Fixes That HelpIf Excel’s TRANSLATE formula is not working, first confirm you are using Microsoft 365 or Excel for the…
- VLOOKUP Not Working? Check Number FormatsYes—VLOOKUP can fail when one lookup value is a number and the matching value is text, even if…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.