COUNTA Not Working in Excel? Fix Common Counting Errors

If COUNTA is not giving the result you expect in Excel, first check what is actually in the cells: it counts any cell that contains a value, text, an error, or a formula—even a formula that displays nothing. To count a range, enter =COUNTA(A2:A20) in an empty cell, replacing the range with yours; then use the checks below to find why the total differs from what you see.

What COUNTA counts in Excel

COUNTA counts cells that are not empty. That includes numbers, dates, text, logical values such as TRUE or FALSE, error values, and formulas. It does not add the values together; it simply returns the number of cells in the specified range that contain something.

For example, with names or other entries in A2:A6, the result is 4 if A2, A3, A5, and A6 contain entries and A4 is genuinely empty. The entries can be different types: a date, a number stored as text, or an error still counts as a non-empty cell. Cell formatting alone does not count. A blank cell filled with a color remains blank to COUNTA.

This distinction explains many apparent failures: COUNTA follows cell contents, not what is visually obvious on the sheet. A cell may look empty but contain a space or a formula, while a cell that looks important because it has a border or fill may contain nothing at all.

Check for formulas that display a blank

A formula that returns an empty text string looks blank, but the cell still contains a formula, so COUNTA includes it. This commonly happens in reports where formulas are filled down but are designed to show nothing until another cell has a value.

For instance, with formulas in B2:B20 that return an empty string when their related input is missing, =COUNTA(B2:B20) counts those formula cells as occupied. The displayed output may be blank even though Excel has a formula stored there. Select one of the cells and inspect the formula bar to confirm.

If the goal is to count only cells whose displayed result has at least one character, and the range contains ordinary text or formula results rather than errors, use a length test. With the values or formulas to assess in A2:A20, enter =SUMPRODUCT(--(LEN(A2:A20)>0)). This returns the number of cells whose result has a positive character length; a formula returning an empty string contributes zero. A cell containing a space still has a character, so it contributes one. If errors are possible in the range, handle those separately because LEN applied to an error can return an error.

Find spaces and other invisible characters

A cell that appears empty may contain one or more spaces, a nonbreaking space copied from a web page, or another invisible character. COUNTA counts these because the cell is not empty. Clicking the cell and looking in the formula bar can reveal a space, although some imported characters are difficult to recognize there.

For a simple check, with a suspect entry in A2, enter =LEN(A2) in another cell. A result greater than zero means Excel sees characters in A2, even if they are not apparent on the sheet. If A2 contains a formula, LEN checks the formula’s result rather than showing how many characters are in the formula itself.

When ordinary leading and trailing spaces are the issue, clean a copy of the data rather than changing the original immediately. With imported text in A2, =TRIM(A2) removes leading and trailing spaces and reduces repeated ordinary spaces between words to one. TRIM does not remove every nonbreaking or special whitespace character, so imported data may need a more specific cleanup. Check the cleaned output before replacing source values, especially when spaces may be meaningful, such as in codes.

Verify the range and the formula syntax

Before changing data, confirm that the formula refers to the cells you intended. A correct function can return an unexpected answer if the range starts one row too low, ends too early, points to another worksheet, or includes an extra column. Click the formula cell and inspect the colored range outline Excel displays while editing the formula. Also check that the formula has not been overwritten with a fixed number.

With entries intended to be counted in A2:A100, =COUNTA(A2:A100) counts that range, but it will not include a new entry in A101. If the list will grow, consider using an Excel table: select the data and choose Insert > Table. Table columns expand as rows are added, and structured references can make formulas easier to maintain. For a quick check, explicitly extend the range and compare the result.

In English-language Excel, the function name is COUNTA and commas separate arguments when a formula has more than one. Some regional settings use semicolons as argument separators; follow the separator Excel expects on that computer. The function name is not case-sensitive, so lowercase or uppercase letters are both acceptable. If Excel displays the formula as text instead of calculating it, check whether the cell is formatted as Text, or whether an apostrophe appears before the equals sign. Change the cell format to General, then re-enter the formula.

Check calculation and displayed results

COUNTA is a straightforward function, so a stale result is more likely to reflect workbook calculation settings than a problem with the function itself. In Excel for Microsoft 365 on Windows, go to Formulas > Calculation Options and choose Automatic. If the workbook is set to Manual, formulas may not recalculate immediately after edits. Press F9 to recalculate open workbooks; this recalculates formulas but does not change the COUNTA range.

Also check that the result is not being hidden or obscured. A narrow column can display number signs for some number formats, and conditional formatting can make text hard to see. Select the result cell and read its value in the formula bar. If you use Excel for the web, formulas recalculate in the browser, but an uploaded workbook may still contain formulas or settings that deserve a check in desktop Excel. Menu labels and shortcuts can vary by platform; the Windows paths above are for Microsoft 365 desktop Excel.

Choose the right counting function

COUNTA is not the right function for every question. Decide whether you need to count filled cells, numeric values, blanks, visible rows, or distinct entries. A mismatch can be a sign that the function is answering a different question from the one you meant to ask.

Count non-empty cells: use COUNTA. This includes text, numbers, formulas, and errors.
Count numbers only: use COUNT. With mixed values in A2:A20, =COUNT(A2:A20) counts numeric entries, including dates stored as Excel numbers, but does not count text entries.
Count blanks: use COUNTBLANK. With the range A2:A20, =COUNTBLANK(A2:A20) counts empty cells and cells whose formulas return an empty string. That means COUNTBLANK and COUNTA can both count a formula-driven cell in their own ways: COUNTA sees the formula, while COUNTBLANK treats its empty-string result as blank.
Count only visible non-empty cells after filtering: use SUBTOTAL with function number 103. With the list in A2:A20, =SUBTOTAL(103,A2:A20) counts visible non-empty cells and ignores rows hidden by a filter; this function number also ignores manually hidden rows.

For a simple list with no filters, COUNTA is usually the clearest choice when every non-empty entry should count. If the question is “How many numeric measurements are present?”, COUNT is more appropriate. If the question is “How many records can I currently see?”, SUBTOTAL may fit better than COUNTA.

Filtered lists, hidden rows, and duplicates

COUNTA counts cells in hidden rows as well as visible rows. Applying a filter changes which records are displayed, not the contents of the range, so the COUNTA total normally stays the same. This often surprises people who expect the total to shrink along with the visible list. Use SUBTOTAL with 103 when the intended count should respond to filtering.

COUNTA also counts duplicates separately. If a column contains the same name three times, it counts three occupied cells; it does not count unique people. In Microsoft 365 or Excel 2021 and later, UNIQUE can help produce distinct values, but counting distinct entries requires more care when blanks or errors are present. For a simple, clean list in A2:A20, =COUNTA(UNIQUE(A2:A20)) counts the distinct values returned by UNIQUE, including a blank if the source range has one. Check the data and expected blank handling before using that result as a record count.

A practical troubleshooting sequence

Work through these checks in order so you do not clean or alter data unnecessarily:

Click the formula cell and confirm that it contains a COUNTA formula, not a typed value or a formula with a different range.
Inspect the range boundaries and compare them with the actual first and last data rows.
Choose a cell that looks blank inside the range. Check the formula bar for a formula, and use LEN in a spare cell to detect hidden characters.
Decide whether formula results that display blank should count. If not, use a method suited to the actual data and test it on a small sample.
Check whether the list is filtered or has hidden rows. Use SUBTOTAL if the count should include visible rows only.
If edits are not reflected, check Formulas > Calculation Options > Automatic and verify that the result cell is not formatted as Text.

For a reliable comparison, make a small test range with one genuine blank, one number, one text entry, and one formula returning an empty string. The expected COUNTA result is three: the number, text, and formula cell count, but the true blank does not. A controlled test makes it easier to distinguish a function issue from unexpected source data.

Frequently Asked Questions

Why does COUNTA count a cell that looks blank?

The cell may contain a formula returning an empty string, a space, or another invisible character. COUNTA checks whether the cell contains something, not whether visible text appears. Select the cell, inspect the formula bar, and use LEN on the cell’s result to look for characters.

Does COUNTA count cells with formulas that return nothing?

Yes. A formula is still present in the cell even when its result displays as an empty string, so COUNTA includes it. If those results should not count, use a test based on the formula result and confirm it handles errors and spaces the way your data requires.

Why does my COUNTA total not change when I filter rows?

COUNTA includes cells in filtered-out rows because they remain in the referenced range. To count only visible non-empty entries, use SUBTOTAL with function number 103 and the same range.

Will COUNTA count numbers stored as text?

Yes. COUNTA counts any non-empty cell, whether the entry is a real number or text that looks like a number. COUNT, by contrast, counts numeric values and does not treat numeric text as a number.

Why is Excel showing my COUNTA formula instead of a result?

The cell may be formatted as Text, the formula may have a leading apostrophe, or Show Formulas may be enabled. Set the cell format to General and re-enter the formula; also check the Show Formulas setting on the Formulas tab if formulas appear throughout the sheet.

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.