COUNTA Function Not Working in Excel? Fix It

If COUNTA seems not to work in Excel, first check that its range includes the cells you intend to count and that you are using the function for nonempty cells, not just numbers. With entries in A2:A20, the basic formula is =COUNTA(A2:A20); it counts cells containing text, numbers, logical values, errors, or formulas—even formulas that display nothing.

Check what COUNTA is supposed to count

COUNTA returns the number of nonempty cells in the values or ranges you give it. It is useful for questions such as “How many cells in this column have something entered?” It does not mean “count numeric values only,” and it does not necessarily mean “count visible records.” Those differences explain many apparently incorrect results.

For example, suppose A2 contains Alice, A3 contains 0, A4 has a formula whose result is an empty string, A5 is truly empty, A6 contains a space, A7 contains an error value, and A8 contains TRUE. With those entries in A2:A8, =COUNTA(A2:A8) returns 7: every cell except the genuinely empty A5 is counted. A zero is a value, a space is text, and an error value still occupies a cell.

COUNTA can accept individual references, ranges, or a mixture of values and references. If you type a number or text directly as an argument, it is counted too. For normal worksheet counting, a range is usually clearest. The function does not change the cells; it only reports how many supplied arguments are nonempty.

Verify the function name, parentheses, and range

A misspelled function name or a reference that does not cover the data can make a correct-looking formula fail. In Microsoft 365 for Windows, select the result cell and inspect the formula bar. Confirm that the name is COUNTA, that the opening and closing parentheses are present, and that the selected range starts and ends where you expect.

For instance, if entries are in B2:B50 but the formula refers only to B2:B20, Excel is counting correctly—it is simply not looking at the later rows. If the range is on another worksheet, include the sheet name and an exclamation mark. With entries on a sheet named Orders in A2:A100, the formula is =COUNTA(Orders!A2:A100). If the sheet name contains spaces, surround it with apostrophes, as in =COUNTA('Quarter 1'!A2:A100).

When the correct range is unclear, select the formula cell and check the colored reference outlines Excel displays while editing the formula. You can also click the range address in the formula bar and select the intended cells again. In a localized Excel installation, the list separator may be a semicolon rather than a comma; use the separator Excel expects for your regional settings if a formula with multiple arguments is rejected.

Check the first and last row in the reference, especially after adding new records.
Look for a missing worksheet name or a reference to the wrong column.
Confirm the cell contains an actual formula, not a text string that only looks like one.

Understand why blank-looking cells get counted

The most frequent surprise is that a cell can look blank without being empty. A formula such as =IF(B2=0,"",B2) displays an empty string when B2 is zero, but the cell still contains a formula. COUNTA counts it. A cell containing one or more spaces is also nonempty, as are cells with an apostrophe-entered text value or a formula result that is an error.

To find these hidden contents, select a cell that appears blank and look at the formula bar. If it contains a formula, spaces, or text, that explains the count. To test a suspect cell, with the suspect entry in A2, enter =ISBLANK(A2) in another cell. It returns TRUE only when A2 is genuinely empty; a formula returning an empty string makes it return FALSE.

If the unwanted spaces are accidental, remove them from the source data rather than changing the count blindly. You can select the affected cells and press Delete to clear their contents, but first make sure you are not removing a formula or meaningful text. For imported data, Excel’s Find and Replace or a cleanup formula may help, but inspect the result before replacing original values. A visible blank and a truly empty cell are not interchangeable in Excel.

Check whether you need numbers, nonblank cells, or visible rows

Choosing the wrong counting function is another common cause. COUNTA counts any nonempty entry. COUNT counts numeric values, including dates and times stored as numbers, but excludes ordinary text. COUNTBLANK counts cells Excel considers blank, including cells with formulas that return an empty string. COUNTIF counts cells that meet a condition.

For example, if A2:A20 contains a mix of names and numbers and the goal is to count numeric entries only, use =COUNT(A2:A20). If the goal is to count cells that are genuinely populated in the usual COUNTA sense, use =COUNTA(A2:A20). If the goal is to count empty cells, use =COUNTBLANK(A2:A20). These functions answer different questions, so switching from one to another should reflect the intended definition of “count.”

Text that looks like a number can also matter. If an imported value such as 123 is stored as text, COUNTA includes it because the cell is nonempty, while COUNT may not treat it as a number. Check the cell’s content and format, and convert text numbers if the task requires numeric calculations. Changing the display format alone does not always convert stored text into a numeric value.

Account for filters and hidden rows

COUNTA counts nonempty cells in hidden rows and in rows excluded by a filter. Filtering changes what is displayed; it does not change the underlying range. As a result, a total made with COUNTA can be larger than the number of visible records.

If you want a count that responds to filtering, use SUBTOTAL with function number 103. With records in A2:A100, =SUBTOTAL(103,A2:A100) counts nonempty visible cells and excludes rows hidden by a filter or hidden manually. Function number 3 also performs a COUNTA-style count that responds to filtering, but it includes manually hidden rows. Choose between 3 and 103 based on whether manually hidden rows should be included.

This distinction is useful in filtered lists, but it is not a correction to COUNTA: the functions have different jobs. Also check whether your range includes a header. If A1 is a heading and the formula covers A1:A100, that heading contributes one to COUNTA. Start at the first data row if you want a record count rather than a count that includes the label.

Investigate errors, formula display, and calculation

COUNTA normally counts error values rather than returning an error just because one of the referenced cells contains #N/A or #DIV/0!. If the result cell shows #NAME?, check the spelling of the function and whether the formula was entered as a formula. A #REF! result points to an invalid reference. If Excel displays the formula text instead of a result, the cell may be formatted as Text, or the formula may have been preceded by an apostrophe.

To correct a formula stored as text, change the result cell’s number format to General, then edit the formula and press Enter. On Windows, Ctrl+` toggles Show Formulas on or off; if that view is enabled, formulas appear in cells instead of calculated results. The key combination can differ by keyboard layout. On Excel for Mac, use the Show Formulas command on the Formulas tab if the shortcut differs on your keyboard.

If the formula is correct but does not update after source cells change, check calculation mode in desktop Excel for Windows: select Formulas > Calculation Options > Automatic. With manual calculation enabled, formulas may remain at their previous results until recalculated. Press F9 to calculate open workbooks, or Ctrl+Alt+F9 to force a full calculation. Excel for the web generally recalculates automatically; if a displayed result still seems stale, check the formula and refresh the workbook view.

Use a reliable troubleshooting sequence

Work from the simplest explanations toward less obvious ones. This keeps you from changing source data or replacing a valid function when the real issue is only an incomplete range.

Select the result cell and confirm the formula begins with an equals sign and spells COUNTA correctly.
Check the highlighted reference and verify that it covers the intended data rows and worksheet.
Inspect cells that look empty; formulas returning an empty string and spaces are counted.
Decide whether hidden or filtered rows should count. If not, consider SUBTOTAL with 103.
Check whether the result cell is displaying a formula as text or whether calculation is set to Automatic.
Compare the result with a small, manually inspected range to identify which entries contribute to the total.

For recurring lists, Excel Tables can make ranges easier to maintain because formulas can refer to table columns rather than fixed row numbers. When new table rows are added, structured references generally expand with the table. Still, verify that the formula refers to the intended column and that its header is not being counted as a data record.

Frequently Asked Questions

Why does COUNTA count a cell that looks blank?
A formula returning an empty string, a space, or other stored content makes the cell nonempty to COUNTA. Check the formula bar or test the cell with ISBLANK; only a genuinely empty cell is ignored.

Does COUNTA count zero?
Yes. A numeric zero is a value, so COUNTA counts it. If you want to count only numeric cells, use COUNT; if zero should be excluded too, use a conditional count that matches your specific requirement.

Does COUNTA count errors such as #N/A?
Yes. An error value is still content in the cell, so COUNTA includes it. If the final count is unexpected, inspect the source cells rather than assuming an error is skipped.

How do I count only visible cells after filtering?
Use SUBTOTAL with function number 103 over the data range. It counts visible nonempty cells and excludes both filtered-out and manually hidden rows. Function number 3 includes manually hidden rows but still responds to filters.

Why is Excel showing my COUNTA formula instead of the answer?
Show Formulas may be on, the result cell may be formatted as Text, or the formula may have been entered with a leading apostrophe. Turn off Show Formulas or change the cell to General, then re-enter the formula and press Enter.

Can COUNTA count text and numbers in the same range?
Yes. That is one of its main uses: it counts nonempty text, numbers, dates, logical values, and errors together. Use COUNT instead when the question is specifically about numeric values.

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.