COUNTA Excel Not Working? Find the Cause

If COUNTA seems wrong, first check what is actually in the cells: the function counts any nonempty cell, including formulas that display nothing, cells containing spaces, and error values. With a list in A2:A6, use =COUNTA(A2:A6); if A2, A4, A5, and A6 contain something while A3 is truly empty, the result is 4.

What COUNTA counts—and what it ignores

COUNTA is designed to count cells that contain information, not just cells with visible text. It counts text, numbers, dates, logical values such as TRUE, errors such as #N/A, and formulas. A zero is still a value, so a cell containing 0 is counted. A formula is counted even when its displayed result looks blank.

A cell that has never contained a value, or whose contents were cleared, is genuinely empty and is not counted. Formatting alone—such as a fill color, border, or number format—does not make a blank cell count as nonempty. This distinction explains many apparent mismatches: the worksheet shows no visible entry, but the cell still contains a formula or an invisible character.

For example, with A2 containing text, A3 truly empty, A4 containing a formula that returns an empty string, A5 containing one ordinary space, and A6 containing the number 0, COUNTA returns 4. It counts A2, A4, A5, and A6. This behavior is intentional, not a defect in the function.

Check the range and the result first

Before changing a formula, confirm that it points to the rows you mean to count. A range that starts at A2 omits a heading in A1; one that ends at A20 will not include later entries. Click the formula cell and inspect its references in the formula bar, or select the range shown in the formula to see which cells are included.

With the intended list in A2:A20, the formula =COUNTA(A2:A20) counts nonempty cells in that range. If exactly 12 of those cells contain values, formulas, or other counted content, the result is 12. Check for a shifted column reference as well: a formula counting A2:A20 will not count entries stored in B2:B20.

Look for an omitted first or last row, especially after adding records.
Make sure the formula references the correct sheet and column.
Check whether headings, notes, or totals are inside the selected range and being counted unintentionally.
Confirm the formula cell displays a calculated result rather than the formula text.

If the displayed total does not update after edits, calculation may be set to manual. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options > Automatic. Also check whether the formula cell is formatted as Text or the formula was entered with a leading apostrophe. Set the cell to General, then re-enter the formula; changing the format alone may not convert text already entered into a working formula.

Formulas that look blank still count

A common cause is a formula that returns an empty string. For example, a report may use a formula to show a result only when another cell has data. When the condition is not met, the formula can display nothing, but the cell still contains a formula. COUNTA therefore includes it.

This is different from clearing a cell. Delete the formula itself to make the cell truly empty. If you need to keep the formula but count only cells that have a visible result, use a different approach tailored to the data; COUNTA alone cannot distinguish a formula returning an empty string from other formulas.

COUNTBLANK can help diagnose the range, but its result is not simply the number of cells with no formulas. With A2 containing text, A3 truly empty, A4 containing a formula that returns an empty string, A5 containing a space, and A6 containing 0, =COUNTBLANK(A2:A6) returns 2: it counts A3 and A4. Compare that with COUNTA’s result of 4 to see why the visible blanks and function totals can appear inconsistent.

Spaces and invisible characters

A cell containing one or more spaces is not empty. The character may be hard to see, particularly when the cell has no border or the space is at the end of a longer entry. Data copied from websites, PDFs, or other systems can also contain nonbreaking spaces or other invisible characters. COUNTA treats such content as present.

For a quick test, select a suspicious cell and look at the formula bar. You can also temporarily edit it and move the cursor through the contents. If the cell contains ordinary spaces, the TRIM function can remove leading and trailing spaces and reduce repeated spaces between words. With three ordinary spaces in A2, =TRIM(A2) returns empty text. That result may look blank, but if you put the TRIM formula in another cell, COUNTA will count that formula cell.

TRIM does not remove every character that looks like a space. In particular, copied nonbreaking spaces may remain. For a small list, carefully inspect or replace the unwanted character; for a larger imported dataset, clean a copy of the data first and verify the cleaned results before relying on the count. Avoid replacing all spaces blindly if spaces are meaningful inside names or descriptions.

Filtered lists and hidden rows

COUNTA counts qualifying cells even when their rows are hidden manually or excluded by a filter. That is often correct for a total inventory of the full range, but it can be surprising when the goal is to count only records currently visible on screen.

For a filtered list in A2:A20 where eight nonempty records remain visible and the other rows are hidden by the filter, =SUBTOTAL(103,A2:A20) returns 8. The 103 option applies COUNTA behavior while excluding filtered-out rows and manually hidden rows. If the requirement is to exclude filtered rows but include rows hidden manually, use function number 3 instead of 103. SUBTOTAL is useful for visible-row counts, but it does not replace COUNTA when the full range should be counted.

Be clear about the question before choosing the function: do you want every populated record, or only the records currently shown? A filtered total can change when the filter changes, while a COUNTA total normally reflects the entire referenced range.

Choose the counting function that matches the question

COUNTA is not a general-purpose count of every kind of item. Excel has several counting functions, and their different rules are useful rather than interchangeable.

COUNTA: Counts nonempty cells, including text, numbers, formulas, and errors.
COUNT: Counts numeric values, including dates stored as numbers; it does not count ordinary text. With the same A2:A6 example above, =COUNT(A2:A6) returns 1 because only A6 contains a number.
COUNTBLANK: Counts empty cells and cells whose formulas return empty text. In the example above, =COUNTBLANK(A2:A6) returns 2.
SUBTOTAL: Can count visible entries in a filtered range when used with its COUNTA option, as described above.

If the goal is to count only entries that meet a condition—such as a particular status or category—use COUNTIF or COUNTIFS rather than trying to make COUNTA do that job. For example, COUNTA counts all populated status cells, whether they say Open, Closed, or something else. A criteria-based function is the appropriate choice when only one category should contribute to the result.

Common formula-entry and data issues

If Excel shows the formula itself in the cell instead of a number, the formula may have been stored as text, or Show Formulas may be enabled. Check the cell format and the formula bar, and verify that the expression begins with an equals sign. If the formula is stored as text, COUNTA referencing that cell will count it as text; it will not calculate it as a formula.

An error in a cell is also counted by COUNTA. For example, a referenced cell displaying #VALUE! is nonempty even though its calculation failed. If the result is unexpectedly high, check for error cells and investigate their source rather than assuming COUNTA will ignore them.

Merged cells can complicate visual checks. Excel stores a merged cell’s content in the upper-left cell of the merged area; the other cells in that merged area are not separate populated entries. A large visual block therefore does not necessarily represent multiple values for COUNTA. When a count matters, unmerge or inspect the underlying cells and use a clearly defined data range.

A reliable troubleshooting routine

Use this sequence to identify the cause without altering the source data prematurely:

Click the COUNTA formula and verify the complete reference, including the sheet name and endpoint.
Inspect cells that look blank for formulas, spaces, error values, or copied characters.
Decide whether hidden or filtered rows should count. Keep COUNTA for the full range; use SUBTOTAL when the intended result is based on visible records.
Compare the result with COUNT or COUNTBLANK if the question concerns numeric entries or blanks rather than all populated cells.
Check that the formula is calculated, the cell is not storing it as text, and calculation is set to Automatic if results are stale.
Clean suspicious imported values in a separate column or copy, then verify the outcome before replacing original data.

This approach also helps prevent a common misconception: a visually blank cell is not necessarily empty, and a cell containing a value is not necessarily visible in the current filtered view. The right count depends on the contents, the selected range, and the question being asked.

Frequently Asked Questions

Why does COUNTA count a cell that looks empty?
A formula returning an empty string, an ordinary space, or an invisible character can make the cell nonempty to Excel. Inspect the formula bar and the cell’s contents; formatting by itself does not make a truly empty cell count.

Does COUNTA count zero?
Yes. Zero is a numeric value, so a cell containing 0 is counted. If you want to count only nonzero numbers, use a condition-based counting formula rather than COUNTA.

Does COUNTA count errors?
Yes. Error values such as #N/A or #VALUE! are still content. Fix or handle the underlying errors if they should not be included in your count.

How do I count only visible rows?
Use SUBTOTAL with function number 103 and the target range to count nonempty visible cells, excluding filtered-out and manually hidden rows. Use 3 if manually hidden rows should still be included while filtered-out rows are excluded.

Why does my COUNTA result not change?
Check that the formula references the edited cells and that calculation is automatic. Also confirm that the displayed expression is an actual formula, not text stored in a cell.

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.