Count Excel Without Blanks: COUNTA and Other Methods

To count cells that are not blank in Excel, use =COUNTA(A2:A20), replacing A2:A20 with your range. COUNTA counts cells containing values, text, errors, or formulas—even formulas that display nothing—so choose a different formula if those formula results or cells containing only spaces should be excluded.

What Excel Means by a Nonblank Cell

Excel distinguishes a truly empty cell from a cell that contains something. A cell may look blank on the worksheet but still contain a formula, a space, or another character. That distinction matters because common counting functions inspect cell contents or results, not just what is visually apparent.

COUNTA counts cells that contain any kind of information, including numbers, text, logical values, errors, and formulas. It does not count genuinely empty cells. A numeric zero is a value, so it counts; a date also counts because Excel stores dates as numbers. A formula returning an empty string is still a formula in the cell, and COUNTA includes it.

For example, suppose a range contains a product name, an actually empty cell, the number 0, a formula that returns an empty string, a cell with one space, the number 12, and another empty cell. COUNTA returns 5. The formula result and the space count even though both may appear blank at normal zoom.

The Basic COUNTA Formula

Use COUNTA when “not blank” means that a cell contains an entry of any type. In Microsoft 365 for Windows, enter the formula in an unused cell and press Enter. The range can be a row, column, or rectangular block; for a whole column, use a reference such as A:A, although a bounded range can be more efficient in large workbooks.

With entries in A2:A20, including numbers, text, errors, and any formulas, use:

=COUNTA(A2:A20)

If A2:A20 has 14 genuinely empty cells and 5 cells with content, the result is 5. COUNTA counts cells, not characters or distinct values: if the same word appears three times in the range, all three cells count. To count a different range, edit both row numbers or the column reference.

You can also select a cell near the data and use the AutoSum drop-down to choose Count Numbers, but that option uses COUNT and counts numeric entries only. It is not a substitute for COUNTA when the range contains text. Entering COUNTA directly is the clearest way to count all nonempty cells.

Exclude Formulas That Display as Blank

A formula such as an IF expression can return an empty string when there is no result to show. The cell looks empty, but COUNTA still counts it because the formula exists. If you want to count cells whose calculated result has at least one character, use LEN inside SUMPRODUCT.

With the values or formulas in A2:A20, use:

=SUMPRODUCT(--(LEN(A2:A20)>0))

For the example range above, the result is 4: the product name, zero, the space, and 12 have results with a length greater than zero; the genuinely empty cells and the formula returning an empty string do not. This formula counts numeric values too because LEN converts their results to text for the length check. It also counts a cell containing one or more spaces, since spaces have length.

If cells containing only ordinary spaces should be treated as blank, trim the values before checking their length. With data in A2:A20, use:

=SUMPRODUCT(--(LEN(TRIM(A2:A20&""))>0))

For the same example, this returns 3: the product name, zero, and 12 remain after trimming, while the formula result and space-only entry are excluded. TRIM removes regular leading and trailing spaces and reduces repeated internal regular spaces to one. It does not remove every possible whitespace character, including some nonbreaking spaces copied from web pages.

Choose the Right Counting Method

The best formula depends on what the word “blank” means in your task. Before choosing, decide whether formulas returning an empty string should count, whether spaces count as entries, and whether you want every cell or only numeric cells.

Count anything entered: COUNTA is the simplest choice. It includes formulas, errors, text, and numbers, while excluding genuinely empty cells.
Count numbers only: COUNT counts numeric values, including dates and times stored as numbers. It does not count text, even if the text looks like a number.
Ignore results with zero characters: SUMPRODUCT with LEN excludes truly empty cells and formulas returning an empty string, but counts spaces.
Ignore empty results and ordinary spaces: Add TRIM around the values in the LEN test. This is useful for imported or manually entered text with accidental spaces.
Count visible entries in a filtered list: SUBTOTAL with function number 103 can count visible nonempty cells in a single range.

COUNTBLANK is the opposite kind of function: it counts cells Excel treats as blank, including formula results that are empty strings. It is useful when the question is how many blanks remain, rather than how many cells contain entries. For a fixed range, the number of cells is the sum of the blank and nonblank counts, provided both calculations use matching definitions and range boundaries.

Count Only Visible Cells in a Filtered List

COUNTA includes cells hidden by a filter and cells hidden manually. If a worksheet is filtered and the goal is to count only the displayed, nonempty entries, use SUBTOTAL with 103, the COUNTA version that excludes manually hidden rows as well as filtered-out rows.

With the list in A2:A20, use:

=SUBTOTAL(103,A2:A20)

The result changes as the filter changes, because rows excluded by the filter are not included. This is different from COUNTA, which still counts those entries. SUBTOTAL also ignores other SUBTOTAL results within its reference, helping prevent some double counting in summary lists. Use 3 instead of 103 only when manually hidden rows should still be included; both options ignore rows removed by a filter.

This formula is for a single range of entries, not a general way to count visible cells across a complicated report. If a filtered table has several columns, first identify which column represents one record—for example, a consistently populated ID column—and count that column. Otherwise, counting across multiple columns may count one visible record several times.

Common Problems and Checks

A result that seems too high usually comes from cells that are not truly empty. Check for formulas returning empty strings, spaces, apostrophe-entered text, or imported characters. Select a suspicious cell and look at the formula bar: a visible formula or text there explains why COUNTA includes it. A formula bar that appears empty does not always make whitespace easy to spot, so testing the LEN of the cell can help.

A result that seems too low may mean COUNT was used instead of COUNTA, the selected range stops before the last data row, or some values are stored outside the expected column. COUNT ignores text. Also check for merged cells: only the upper-left cell of a merged area stores the content, so the other cells in that visual block are empty for counting purposes.

Errors inside the range are not blank. COUNTA includes them as populated cells. By contrast, the LEN and TRIM formulas shown above can return an error if an input cell contains an error, because the text-length calculation cannot evaluate that error as ordinary text. If the range may contain errors, decide whether to exclude them or count them and handle those cells separately; do not assume they are empty.

To inspect spaces, you can temporarily use a helper column. With the original value in A2, enter =LEN(A2) in another cell and fill down. A result of 0 indicates a zero-length result, while a positive number indicates characters; a single ordinary space returns 1. This check is especially useful when data was copied from email, websites, or exported reports.

Use the Formula in Tables and Changing Ranges

If your data is an Excel Table, a structured reference can make a count easier to maintain as rows are added. Suppose the table is named Sales and the column to count is named Item. With records in Sales[Item], use:

=COUNTA(Sales[Item])

This counts nonempty cells in the Item data column and normally adjusts as table records are added or removed. Replace Sales and Item with the actual table and column names. A table’s total row can also provide summaries, but its calculation depends on the selected summary function; verify that it is counting text as well as numbers when that is required.

For a regular range, a reference such as A:A automatically includes later entries in column A, but whole-column calculations can be less efficient when many formulas process every row. A bounded range such as A2:A5000 is often a practical balance. If the data grows unpredictably, converting it to a Table is usually easier to maintain than continually editing the end row in several formulas.

Frequently Asked Questions

Does COUNTA count a formula that returns an empty string?
Yes. COUNTA counts the formula cell even when the formula displays an empty string. Use the LEN and SUMPRODUCT approach if those results should be excluded.

Does COUNTA count zero?
Yes. Zero is a numeric value, not a blank. If you want to exclude zero as well as blanks, you need a condition that tests for nonzero values rather than a simple COUNTA count.

How do I count cells that are not blank but ignore spaces?
Use the TRIM-and-LEN formula for the range. It ignores cells whose only content is regular spaces and also excludes formulas returning an empty string. Be aware that it may not remove special whitespace characters.

What is the difference between COUNT and COUNTA?
COUNT counts numeric entries, including dates and times. COUNTA counts any nonempty entry, including text, errors, logical values, and formulas. Choose based on the type of content you want included.

Will COUNTA update when I add more data?
It recalculates when cells within its referenced range change, but it cannot count entries added below a fixed range such as A2:A20. Use an Excel Table reference or expand the range if new records may be added.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.