COUNTA Not Working in Excel? Causes and Fixes

COUNTA often seems not to work because it counts every cell that is not truly empty—including cells with formulas that return an empty string, spaces, errors, or values in hidden rows. If your entries are in A2:A100, enter =COUNTA(A2:A100) in an empty cell; the result is the number of nonempty cells in that range, not necessarily the number of visible or meaningful entries.

What COUNTA counts—and what it skips

COUNTA is Excel’s function for counting nonempty cells. It accepts cell references, ranges, and values supplied directly as arguments. A genuinely empty cell is left out. A cell containing text, a number, a date, a logical value such as TRUE, or an error value is counted. Zero is a number, so it counts too. The cell’s display format does not change this: a value hidden by a custom number format is still present and counted.

One frequent surprise is a formula that displays nothing. If a cell contains a formula whose result is the empty text string, the cell still contains a formula, so COUNTA counts it. A cell containing one or more spaces is also nonempty. The same principle applies to invisible characters pasted from a website or another system: if a character is stored in the cell, COUNTA sees content even if it looks blank.

For example, suppose A2 contains Apple, A3 contains a formula returning an empty string, A4 contains 0, A5 contains a space, A6 contains an error, and A7 is genuinely empty. With those contents in A2:A7, =COUNTA(A2:A7) returns 5. The formula counts A2 through A6 and skips only A7. This is usually the key to understanding an unexpected result.

Check the range and formula entry

Before changing the data, make sure the formula points to the cells you intend to count. A reference such as A2:A100 includes 99 rows, while A2:A99 includes 98. A range can also be on the wrong worksheet, start one row too high, or omit newly added records. Click the formula cell and inspect the reference in the formula bar; select the range in the formula to see which cells Excel highlights.

Also check that the formula is entered as a formula rather than stored as text. If the cell displays the characters =COUNTA(A2:A100) instead of a result, it may have been formatted as Text before entry, or it may have a leading apostrophe. Change the cell format to General, edit the cell, and press Enter. If formulas appear instead of results throughout the worksheet, the Show Formulas setting may be on; toggle it from the Formulas tab or with Ctrl+` on Windows. On a Mac, the shortcut can depend on the keyboard layout, so use the Show Formulas control on the Formulas tab if needed.

A function name or punctuation error can also prevent Excel from calculating the expression. The function is spelled COUNTA, with no space. In Microsoft 365 for Windows, you can insert it through Formulas > More Functions > Statistical > COUNTA. If the formula produces an error rather than a count, read the error message and check that the parentheses and range references are complete. Excel installations using a different regional list separator may require a semicolon between separate arguments, although a single-range formula such as the example above has no argument separator to change.

Find formulas, spaces, and invisible content

When a cell looks empty but contributes to the total, inspect its contents rather than its appearance. Select the cell and look at the formula bar. A formula may be present, or the cell may contain a space, a nonbreaking space, or another character that is difficult to see. Copying data from web pages, reports, and exported files is a common source of such characters.

For a quick test of one suspect cell, with the suspect content in A2, enter =ISBLANK(A2) in another cell. It returns TRUE only if A2 is genuinely empty; it returns FALSE if A2 contains a formula, including one that displays an empty string. To check for ordinary characters such as spaces, with the suspect value in A2, enter =LEN(A2). A result of 1 can indicate a single space, while a formula returning an empty string has a length of 0. These tests help explain what is in a cell, but a zero length does not make a formula cell truly blank for COUNTA.

If the unwanted contents are accidental, remove or clean them carefully. Pressing Delete clears a selected cell’s contents; it does not remove the cell’s formatting. For imported data, use Find and Replace or a cleanup formula appropriate to the exact character and data source. Do not replace every apparent blank without checking: some formulas deliberately return empty strings to keep a report tidy, and deleting them would remove logic the workbook may need.

Hidden rows, filters, and merged cells

COUNTA counts cells in hidden rows and in rows excluded by a filter, provided those cells fall inside the referenced range. Filtering changes what is displayed, not what COUNTA includes. This is a common reason a total seems too large when a reader expects it to describe only the currently visible records.

For a filtered list where names or other entries are in A2:A11, use =SUBTOTAL(103,A2:A11) to count nonempty cells in visible rows. If seven visible rows in that range contain entries, the result is 7. The 103 option uses COUNTA behavior while excluding rows hidden by a filter and rows hidden manually. SUBTOTAL also ignores nested SUBTOTAL results in its reference, which helps avoid double-counting in some summary layouts. Make sure the range covers the intended list and that each record has a value in the chosen column; otherwise, a visible record with a blank in that column will not be counted.

Merged cells can create a different mismatch. In a merged area, the value is stored in the upper-left cell; the other cells in the merged area do not hold separate values. A COUNTA range covering the merged area therefore counts the stored entry once, not once for every cell the merged area spans. For data tables, avoiding merged cells generally makes sorting, filtering, and counting more predictable.

Calculation settings and workbook behavior

COUNTA normally updates when its referenced cells change. However, a workbook set to manual calculation can leave formula results stale until Excel recalculates. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options and choose Automatic if that suits the workbook. Pressing F9 recalculates formulas when calculation is manual. Be aware that calculation mode can affect other formulas in the workbook too; large models may use manual calculation deliberately to avoid repeated recalculation.

Calculation settings do not change the core counting rule: COUNTA counts a cell containing a formula even when that formula currently displays an empty string. A stale source formula can make related results look out of date, but changing the calculation setting will not make formula cells count as blank. If the number remains unexpected after recalculation, inspect the range and its contents.

Workbook links and imported data can add another layer. A cell may contain a formula linked to another sheet or workbook, or a query may have loaded text that looks empty. COUNTA evaluates the resulting cell contents in the current workbook; it does not decide whether a value is useful, current, or visible. Check the source and refresh or update external data only when appropriate for the workbook.

COUNTA compared with related counting functions

Choose the counting function based on what you mean by “count.” COUNTA counts nonempty cells, while COUNT counts cells containing numbers. COUNT therefore excludes ordinary text such as names, even though COUNTA includes it. COUNTBLANK counts blank cells; Excel also treats formulas returning an empty text string as blank for COUNTBLANK, so its result can differ from what COUNTA leads you to expect.

For example, if a list contains product names as well as numbers, COUNTA is appropriate when you want the number of filled records. If you want only numeric entries, COUNT is the closer match. If you need to count cells meeting a condition—such as a particular status—COUNTIF or COUNTIFS may be more suitable. These functions answer different questions, so swapping one in without defining the intended count can create another misleading total.

Use COUNTA for cells with any stored content, including text and errors.
Use COUNT for numeric cells, including dates stored as Excel serial numbers.
Use COUNTBLANK when you need the number of blank cells, including cells whose formulas return an empty string.
Use SUBTOTAL with function number 103 when you need a COUNTA-style count that responds to filtered or hidden rows.

A reliable troubleshooting sequence

Work through the issue in a consistent order rather than deleting cells immediately. First decide what should count: all nonempty cells, numeric cells, or only visible records. Then verify the range and compare the result with a small sample you can inspect. If the sample includes a formula returning an empty string or an invisible character, that is likely the explanation rather than a COUNTA defect.

Click the formula cell and verify that it begins with an equals sign and references the intended worksheet and range.
Inspect cells that look blank using the formula bar, then test suspicious cells with ISBLANK or LEN.
Check whether hidden or filtered rows should be included. If not, consider SUBTOTAL with 103.
Confirm whether the desired count is all content or only numbers, and choose COUNTA, COUNT, or another function accordingly.
If results are stale, check calculation mode and recalculate; then verify the result against a small, known set of cells.

For a small test range, manually list the cells that should count and compare that list with Excel’s result. This is more dependable than assuming that every cell that looks blank is empty. Once the underlying cause is identified, fix the source data or adjust the counting method rather than masking the discrepancy with an unexplained offset.

Frequently Asked Questions

Why does COUNTA count cells that look blank?
A cell can look blank while containing a formula that returns an empty string, a space, or an invisible character. COUNTA counts it because the cell is not truly empty. Inspect the formula bar or use ISBLANK and LEN to investigate.

Does COUNTA count zero and errors?
Yes. Zero is a numeric value, and an error such as #N/A is still content in the cell. Both are counted when they are inside the referenced range.

Does COUNTA ignore filtered or hidden rows?
No. COUNTA includes nonempty cells in hidden rows and filtered-out rows. For a visible-row count, SUBTOTAL with function number 103 provides COUNTA-style counting that excludes filtered and manually hidden rows.

Why does COUNTA return 0 when I can see a formula?
Check that the formula’s range includes the cell you are looking at and that the formula cell is not displaying text rather than calculating. Also confirm that the value is actually in a cell within the range; a chart label, shape, or text box is not cell content and will not be counted.

Is COUNTA the same as COUNT?
No. COUNTA includes text, numbers, logical values, and errors in nonempty cells. COUNT is for numeric values. For a list of names or mixed entries, COUNTA is usually the relevant choice.

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.