COUNTA Formula Not Working? Fix Common Excel Issues

COUNTA often seems not to work because it counts cells containing formulas that return an empty string, spaces, errors, or hidden data as nonempty. Check that your range is correct, then decide whether you want to count every nonempty cell or only cells with visible content; with values in A2:A20, enter =COUNTA(A2:A20) to count nonempty cells in that range.

What COUNTA counts in Excel

COUNTA counts cells that contain any kind of information: text, numbers, dates, logical values such as TRUE, errors, and formulas. It does not count genuinely empty cells. The key distinction is between a cell that is empty and a cell that contains something that looks empty on the worksheet.

For example, a formula that returns an empty string still exists in its cell, so COUNTA includes that cell. A cell containing one or more spaces also counts. Zero is a value, not a blank, and an error such as #N/A counts too. By contrast, a cell that has never had a value entered, or whose contents have been cleared, is not counted simply because it has formatting such as a fill color or border.

This behavior makes COUNTA useful for counting entered records, but it can give a larger result than expected when a worksheet uses formulas to make unused rows look blank. It is not a visual-content detector: it checks whether a cell contains a value or formula, not whether a person can see text in the cell.

Check the range and the formula entry

A simple range mistake is one of the first things to rule out. Make sure the formula points to the intended worksheet and includes the rows where your data actually sits. A range that starts at row 2 will not count a value in row 1; a range ending at row 20 will miss later entries. Also check that the formula is entered as a formula, beginning with an equals sign, rather than stored as text.

For a concrete example, suppose A2 contains Ada, A3 is genuinely empty, A4 contains a formula that displays nothing, A5 contains a space, A6 contains 0, and A7 contains #N/A. With those values in A2:A7, the formula =COUNTA(A2:A7) returns 5: it counts Ada, the formula, the space, zero, and the error, but not the empty A3 cell.

If you need to count more than one separate area, list the ranges as separate arguments. With 14 nonempty cells in A2:A20 and 9 in C2:C20, =COUNTA(A2:A20,C2:C20) returns 23. Check that your selected areas do not overlap, or cells in an overlapping area may be counted twice. In some regional settings Excel displays semicolons rather than commas between function arguments; use the separator your installation expects.

Find formulas that look blank

To locate cells that COUNTA includes unexpectedly, select a suspicious cell and inspect the formula bar. A formula such as an IF statement may return an empty string when no data is available. The cell looks blank, but it still contains a formula. Similarly, pasted data can include a space or other invisible character. Selecting the cell and pressing F2 can help reveal that it contains something; pressing End and then Backspace can remove trailing spaces during editing, but take care not to delete real content.

For a quick audit, temporarily use a helper column to check whether a cell contains a formula. If the formula bar shows a formula in a visually empty cell, that explains why COUNTA includes it. To clean imported text, Excel’s TRIM function can remove ordinary leading, trailing, and repeated spaces, although it does not remove every kind of nonbreaking or unusual whitespace. For data from websites, a dedicated cleanup step may be needed before counting.

Do not confuse display formatting with cell contents. A custom number format can hide a number, and white font on a white background can hide text, but both values are still counted. Changing the display format does not make the underlying cell empty.

Count only cells with visible content

If formulas returning an empty string should be treated as blank, use a test based on the length of the displayed text rather than COUNTA. With ordinary values and no errors in A2:A20, =SUMPRODUCT(--(LEN(A2:A20)>0)) counts cells whose contents have a length greater than zero. For example, if A2:A6 contain Ada, a truly blank cell, a formula returning an empty string, a single space, and zero, this formula returns 3: Ada, the space, and zero.

This is a different definition of nonblank, not a universal replacement for COUNTA. A space has a length of one, so the formula still counts whitespace-only cells. Errors in the checked range can also cause a length-based calculation to return an error. If your goal is to count meaningful names or descriptions, clean the source data and decide how to handle spaces and errors rather than assuming that every nonempty value is valid.

If a count should exclude rows hidden by a filter, use a visibility-aware function instead. With records in A2:A20, =SUBTOTAL(103,A2:A20) counts visible nonempty cells and excludes filtered-out rows; function number 103 also excludes manually hidden rows. COUNTA itself counts hidden rows. SUBTOTAL still follows its nonempty-cell logic, so formulas returning empty strings may need separate handling if they are part of the range.

Understand hidden rows, filters, and tables

Filtering a list changes which rows are displayed, not the values stored in those rows. That is why a COUNTA total generally stays the same when you apply a filter: it counts the whole referenced range. Use SUBTOTAL when the intended result is a count of records currently visible after filtering. If manually hidden rows should remain included, SUBTOTAL function numbers 1 through 11 behave differently from 101 through 111; 103 is the version that ignores manually hidden rows as well as filtered-out rows.

Excel tables can make the range easier to manage as data grows. If a table is named Table1 and its column is Status, the structured reference =COUNTA(Table1[Status]) counts nonempty cells in the table’s Status data column. A table reference can reduce the chance that newly added records fall outside a fixed range, but it does not change what COUNTA considers nonempty. Formula-generated empty strings and spaces are still important to check.

Choose the right counting function

The best function depends on what the result is supposed to mean. Excel’s counting functions have different rules, so replacing COUNTA without checking those rules can create another misleading total.

COUNTA: Counts cells containing any value or formula, including text, numbers, errors, spaces, and formulas returning an empty string.
COUNT: Counts numeric values, including dates stored as numbers, but does not count text or errors as numbers.
COUNTBLANK: Counts blank cells, including cells with formulas that return an empty string. A cell containing a space is not blank.
SUBTOTAL: Can count visible nonempty cells when you use function number 103, which is useful for filtered lists.

For example, if the question is how many cells in a status column have been filled in at all, COUNTA is usually appropriate. If the question is how many numeric sales amounts were entered, COUNT may be more suitable. If the question is how many records are visible after filtering, use SUBTOTAL. If the question is how many cells are empty-looking because formulas return nothing, identify whether those formulas should count as occupied or blank before choosing a method.

Common causes and practical fixes

When the total is too high, inspect formulas returning empty strings, spaces, hidden rows, and errors first. When it is too low, check whether the range stops before the last record, starts on the wrong row, or points to the wrong sheet. A text-formatted formula may appear in the cell instead of calculating; change the cell format to General, then re-enter the formula. If Excel is set to manual calculation, formulas may not refresh until calculation is triggered; press F9 on Windows to recalculate the workbook. On Mac, use Fn+F9 on keyboards where function keys require the Fn key.

Use these checks in order:

Select the COUNTA formula cell and confirm that the formula begins with an equals sign and refers to the intended range.
Click cells that look blank but are included, and inspect the formula bar for a formula, spaces, or another value.
Check the first and last rows of the range, and confirm that the formula is not counting extra headers or unrelated notes.
Decide whether hidden or filtered records belong in the answer; use SUBTOTAL for a visible-row count when appropriate.
Recalculate if the workbook uses manual calculation, and verify the result against a small sample whose contents you can inspect.

For Windows, F2 edits the selected cell and F9 recalculates formulas; Mac keyboard behavior can depend on the keyboard’s function-key settings. In Excel for the web, formulas recalculate in the browser, but some desktop keyboard shortcuts may be handled by the browser or operating system. The formulas themselves work in current Microsoft 365 Excel, though menu and keyboard behavior can vary by platform.

Frequently Asked Questions

Why does COUNTA count a cell that looks empty?
A formula returning an empty string or a space makes the cell nonempty to COUNTA. Check the formula bar and remove unwanted contents, or use a different test if those cells should be treated as blank.

Does COUNTA count zero?
Yes. Zero is a numeric value, so COUNTA includes it. If you want to count only nonzero numbers, use a method designed for that condition rather than treating zero as blank.

Does COUNTA count errors?
Yes. Error values such as #N/A are still contents of a cell, so COUNTA counts them. The error does not usually stop COUNTA from returning a count.

Why does my COUNTA total change when I edit a cell?
Editing may remove a space or formula, add a value, or change a formula from returning an empty string to returning content. Check what the cell contains, not only what it displays, and confirm that automatic calculation is enabled if totals seem stale.

How do I count only visible cells after filtering?
For a range such as A2:A20, use =SUBTOTAL(103,A2:A20). It counts visible nonempty cells and ignores filtered-out and manually hidden rows, unlike COUNTA.

Can COUNTA count cells across multiple ranges?
Yes. Give each range as an argument, separated by the list separator used by your Excel settings. Make sure the ranges do not overlap if you do not want any cell included twice.

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.