COUNTIF Spill Error in Excel: Causes and Fixes

Errors & fixes · Updated November 19, 2026

A COUNTIF formula usually returns one result, but it can show a #SPILL! error when its criteria argument contains multiple values and Excel needs to return several counts into neighboring cells. To fix it, clear the cells where the results should spill, move the formula outside an Excel Table, or use one criterion cell instead of a range if you need only one count.

Why COUNTIF can produce a spill error

COUNTIF counts cells in a range that meet a condition. With a single criterion, it returns a single number. In Microsoft 365, however, a formula can also use a range of criteria and return an array of separate counts. Excel tries to place those results in adjacent worksheet cells. If it cannot, the formula cell displays #SPILL!.

For example, suppose A2:A8 contains apple, pear, apple, plum, pear, apple, and kiwi, while D2:D4 contains apple, pear, and plum. With the formula entered in F2, Excel can return three counts vertically in F2:F4: 3, 2, and 1. The formula uses the three criteria in D2:D4, rather than combining their counts into one number.

With those entries in A2:A8 and criteria in D2:D4, enter this formula in F2 to return one count per criterion:

=COUNTIF(A2:A8,D2:D4)

That behavior is called dynamic array spilling. It is useful when you want a list of results from one formula, but it means cells below or beside the formula must be available. A COUNTIF formula with a single criterion, such as a reference to D2 alone, normally returns one value and does not need a spill range.

How to identify the cause

Start by selecting the cell showing #SPILL!. Excel may display a warning icon beside it; open the error menu for a description of the problem. A dashed outline can show the area where Excel intends to place results. Look inside that outline before deleting or moving anything, because the cells may contain data you need.

Common reasons the result cannot appear include:

  • Occupied cells: A value, formula, or even a space in a destination cell can block the array. Formatting alone generally does not block a spill.
  • Merged cells: A spill range cannot expand through merged cells. Unmerge the relevant cells or put the formula somewhere else.
  • An Excel Table: Dynamic array formulas cannot spill from within a table body. A formula using an array of criteria may therefore fail there.
  • A result range that changes: If the criteria range contains several cells, Excel may need more output cells than are currently clear.

Check the formula bar as well. A reference such as D2:D4 signals that the formula may be asking Excel for multiple counts. A reference such as D2 is a single-cell criterion. The difference is easy to miss when a formula was copied or when a cell reference was expanded during editing.

Fix a blocked spill range

If you want a separate count for every criterion, keep the array formula and make room for the results. Check the cells in the outlined spill area for values, formulas, spaces, or merged cells. Move existing content if appropriate, or relocate the COUNTIF formula to an empty part of the worksheet. Avoid clearing an entire range until you have checked whether it contains useful information.

For example, with data in A2:A8, criteria in D2:D4, and the formula in F2, the three results need F2:F4. If F3 contains a note, Excel cannot display the second count there. Move the note or choose a clear output area. After removing the obstruction, the results should appear automatically; you do not need to fill the formula down.

If Excel identifies a merged cell as the obstacle, select the affected cells and use Home > Merge & Center > Unmerge Cells on Windows. The Mac ribbon has the corresponding Merge & Center control on the Home tab. Unmerging may change the layout, so consider moving the formula instead when the merged design needs to stay intact.

If a spill is blocked by a table, place the formula in a normal worksheet cell outside the table, with enough empty cells for the results. You can also change the formula so it uses one criterion at a time. Do not confuse a table’s calculated-column behavior with dynamic spilling: table formulas typically fill down as calculated columns, while a multi-result dynamic array needs a clear spill area outside the table.

Use one criterion when you need one answer

Sometimes a spill is not the result you want. If you want the number of cells matching just one selected criterion, refer to one cell rather than a criteria range. With values in A2:A8 and the criterion apple in D2, enter this in F2:

=COUNTIF(A2:A8,D2)

The result is 3 for the example data. If you copy the formula down to count each criterion in D2:D4, make the data range absolute so it stays fixed. With the same data and criteria, enter this in F2 and copy it down through F4:

=COUNTIF($A$2:$A$8,D2)

This returns 3, 2, and 1 in separate rows. The dollar signs lock the data range while the criterion reference changes from D2 to D3 and D4. This is a useful alternative when you prefer ordinary copied formulas or need each result placed in a particular row.

For a single rule involving a comparison, COUNTIF can take a text criterion that includes an operator. With numbers in B2:B20, this formula counts values at least 100:

=COUNTIF(B2:B20,">=100")

Here the result depends on the actual numbers in B2:B20. The comparison operator and number are enclosed in quotation marks because they are being supplied as a criterion string. If you want the threshold to come from a cell, join the operator to that cell reference instead.

Count matches across a list of criteria

A spilled list of counts and a combined total are different outputs. If you want to see each category’s count, use the array-criteria formula and leave space for the results. If instead you want one total equal to the sum of the individual counts, wrap COUNTIF in SUM. With data in A2:A8 and distinct criteria in D2:D4, enter:

=SUM(COUNTIF(A2:A8,D2:D4))

For the example list, this returns 6: three apples, two pears, and one plum. SUM reduces the array of counts to one number, so there is no multi-cell result to spill. This total counts matches by criterion, which matters if the criteria list contains duplicates: repeating apple would count the apples again. Remove duplicate criteria if the intended total is the number of source cells matching any distinct listed category.

There is another important distinction: COUNTIF evaluates one condition at a time. If a task requires two conditions to be true for the same row, use COUNTIFS rather than adding separate COUNTIF results. COUNTIFS can also return multiple results when one of its criteria arguments is an array, so the same spill rules can apply. Choose the function based on the logic you need, not just because it appears to avoid an error.

Other COUNTIF issues that look like spill problems

Not every unexpected COUNTIF result is a spill error. A result of zero, an incorrect count, or a formula displayed as text has a different cause. Check that the formula cell does not have a leading apostrophe, that calculation is enabled, and that the ranges cover the intended rows. Also check for extra spaces or inconsistent values in the source data.

COUNTIF is not case-sensitive for ordinary text comparisons. It also supports two wildcards: an asterisk matches any number of characters, and a question mark matches one character. To count values that begin with the text North in A2:A20, use:

=COUNTIF(A2:A20,"North*")

This counts entries such as North and Northeast, but it does not count a value with a different beginning. A literal asterisk or question mark in the data needs a tilde escape in the criterion. These wildcard rules affect what is counted; they do not, by themselves, create a spill range.

COUNTIF also expects its range and criteria arguments in that order. It will not add counts just because a criterion range contains several entries; it returns individual counts unless another function, such as SUM, combines them. Keeping that distinction in mind helps separate a formula-design issue from a blocked-cell issue.

Excel version and platform considerations

This guidance is for Excel for Microsoft 365 on Windows. Dynamic arrays make formulas that return multiple results spill into nearby cells. Excel 2021 also supports many dynamic array behaviors, though available functions and updates can differ from Microsoft 365. Older Excel versions may handle array formulas differently and may not show the same spill behavior or error message.

The underlying worksheet checks are similar in Excel for Mac and Excel for the web: inspect the intended output cells and avoid placing a spilling formula inside a table. Ribbon labels and error-help details can vary slightly by platform or update. On Windows, a useful way to remove an obstruction is to select the specific blocking cell and clear or move its contents; do not delete the cell itself unless changing the sheet layout is intended.

Frequently Asked Questions

Does COUNTIF normally return a spill error?
Not when it has one criterion and returns one result. A spill is more likely when the criteria argument is a multi-cell range or array and Excel needs to return multiple counts, but cannot place them in the required cells.

How do I find which cell is blocking the spill?
Select the error cell and inspect Excel’s spill outline and error menu, if available. Check the outlined destination cells for contents or merged cells. A cell that looks blank may contain a space or a formula returning an empty string.

Can I use COUNTIF inside an Excel Table?
Yes, a normal single-result COUNTIF can be used in a table. But a dynamic array formula cannot spill within a table body. Put a multi-result formula outside the table or use one criterion per row in a calculated column.

How can I count several criteria without getting multiple results?
For distinct criteria listed in a range, use SUM around COUNTIF to combine the counts into one value. If duplicate criteria are present, they can inflate that sum because each criterion contributes its own count.

Will deleting the cells beside the formula always fix it?
No. It helps only if those cells are blocking the intended output and are safe to clear. Merged cells, a formula located inside a table, or an unwanted array of criteria may need a different fix. Check what the formula is meant to return before changing the worksheet.

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.