Excel Count Nonblank Cells: Formulas and Examples

Formulas & functions · Updated November 18, 2026

To count nonblank cells in Excel, use =COUNTA(A2:A20), replacing the range with the cells you want to check. COUNTA counts cells containing text, numbers, dates, errors, or formulas—even a formula that displays an empty string—so it is important to know what Excel regards as nonblank before choosing a method.

Use COUNTA for a standard nonblank count

COUNTA is the usual answer when you want to count cells that contain something. It works with a contiguous range or a selection of separate references, and it does not require the cells to have the same type of content. For example, a list can mix names, dates, amounts, and notes.

With the example values North, a truly empty cell, 0, a formula returning an empty string, a cell containing one space, an error value, and 12 in A2:A8, =COUNTA(A2:A8) returns 6. The empty cell is left out; each of the other six cells is counted. The formula can go in any cell outside A2:A8.

For a quick check, select the range and look at Excel’s status bar, which may show a count for the selection. The status bar is useful for a one-time glance, but a COUNTA formula is better when the result needs to update as the data changes or appear in a report.

What Excel considers nonblank

“Nonblank” can mean either that a cell contains something in the worksheet or that it visibly shows content to a person. COUNTA uses the first meaning. The distinction explains many surprising results:

  • Text and numbers: Both are counted. A numeric zero is a value, even if a number format makes it look like a dash or hides it.
  • Dates: Excel stores dates as numbers, so COUNTA counts them just as it counts other numeric values.
  • Errors: Cells displaying errors such as #N/A or #VALUE! are counted. COUNTA counts that the cell contains an error; it does not evaluate whether the underlying calculation is correct.
  • Formulas: A cell containing a formula counts even if the formula’s displayed result appears empty. This includes a formula that returns an empty text string.
  • Spaces: A cell containing one or more spaces is not truly empty, so COUNTA counts it. It may look blank until you select the cell or inspect the formula bar.
  • Formatting alone: A genuinely empty cell does not become nonblank just because it has a fill color, border, or number format.

This means a worksheet can look empty in places while COUNTA still reports a positive count. It also means COUNTA is not a test for whether a row is complete or whether a displayed value is meaningful. The right formula depends on what you intend to count.

Count cells that display a value

If a column contains formulas that return empty strings and you want to exclude those results, use a test based on the length of the displayed text instead of COUNTA. With possible values in A2:A20, this formula counts cells whose results have at least one character: =SUMPRODUCT(--(LEN(A2:A20&"")>0)). It counts zero because zero becomes the text “0,” but it does not count a genuinely empty cell or a formula result of an empty string.

In that formula, the &"" part converts values to text so LEN can test them consistently. A cell with a space still has a character and is counted. If errors might occur in the range, they can cause this version to return an error too. With potentially erroneous values in A2:A20, use =SUMPRODUCT(--(IFERROR(LEN(A2:A20&""),0)>0)) to treat error cells as having zero length and exclude them. Choose this deliberately: it does not count errors as meaningful entries.

To exclude cells that contain only ordinary spaces as well, and to ignore errors, use =SUMPRODUCT(--(LEN(TRIM(IFERROR(A2:A20&"","")))>0)) with data in A2:A20. TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one, so a cell made only of spaces becomes empty for this test. This is more specialized than COUNTA and may not be necessary for a clean data list. TRIM does not remove every kind of whitespace, such as all nonbreaking spaces copied from web pages.

These SUMPRODUCT approaches are useful when the meaning of “nonblank” is “has a visible, non-error result,” but they are more complicated than COUNTA. They also test the value returned by a formula, not whether the cell contains a formula. Keep the formula range bounded to the actual data rather than applying these array calculations to an entire worksheet column.

Count nonblank cells in filtered or hidden rows

COUNTA includes cells in rows hidden by a filter and cells in rows hidden manually. That is appropriate for a total across the full dataset, but not if you want a count of only the records currently visible in a filtered list.

With the records in A2:A100, use =SUBTOTAL(103,A2:A100) to count nonblank cells in visible rows. Function number 103 applies COUNTA while excluding rows hidden by a filter and rows hidden manually. If you use function number 3 instead, filtered-out rows are still excluded, but manually hidden rows are included. SUBTOTAL also ignores other SUBTOTAL results in its reference, which helps avoid double-counting in grouped calculations.

Place the formula outside the filtered range so it remains easy to see. The counted column should be one that is populated for every record you mean to count. If you use a column that is sometimes legitimately empty, the result will count visible nonblank cells in that column—not necessarily visible records.

How COUNTA compares with COUNT and COUNTBLANK

Excel has several counting functions because “count cells” can describe different questions. Choose based on the content you want included:

  • COUNTA: Counts cells containing any kind of content, including text, numbers, errors, and formulas that return empty text.
  • COUNT: Counts numeric values, including dates stored as numbers. It is not a general count of filled-in cells; text entries are not included.
  • COUNTBLANK: Counts empty cells in a range. It also treats formulas returning an empty text string as blank for this purpose, which is why its result may not simply be the opposite of COUNTA.
  • SUBTOTAL with 103: Counts nonblank cells in visible rows, rather than counting every row regardless of filtering or manual hiding.

For example, a column containing names should generally use COUNTA, while a column of sales amounts may use COUNT if you specifically want the number of numeric entries. If the aim is to find missing entries, COUNTBLANK may be more direct. A blank count can also include formula results that look empty, so inspect the formula behavior if the blank total seems unexpected.

Common problems and ways to avoid them

A frequent source of confusion is a formula that displays nothing. If a cell contains a formula such as an IF calculation that returns an empty string under some conditions, COUNTA still counts the formula cell. That is not a bug: the cell contains a formula even though its visible result is empty. Use a LEN-based formula when you specifically want to count visible results, or use COUNTA when the presence of a formula itself should count.

Another common issue is stray spaces. They can arrive when data is pasted from another source and make apparently empty cells count as filled. Before changing the count formula, check a few suspicious cells by selecting them and looking at the formula bar. You can also clean a copy of the data with Excel’s data-cleaning tools or a helper formula, taking care not to remove spaces that are meaningful in the source.

Also check the range boundaries. A formula such as =COUNTA(A:A) counts the entire column, including a heading and any other content anywhere in column A. If the formula itself is placed in column A, it can create a circular reference. A specific range such as A2:A100 is easier to audit and avoids accidentally counting a heading or unrelated note. When the data grows regularly, an Excel Table can help keep the range aligned with the records.

Finally, formatting is not content. A colored cell with no value is still blank to COUNTA, while an invisible space is content. If a formula result disagrees with what you see, check for formulas, spaces, hidden rows, and a heading included in the selected range before assuming Excel has miscounted.

Use COUNTA with a growing list or table

For a small, fixed list, a cell range such as A2:A100 is straightforward. If new records are added often, format the dataset as an Excel Table using Insert > Table on the Windows ribbon, or use Home > Format as Table. Include the header row and confirm that the table has headers when prompted. Tables expand as you add records, which makes formulas and references easier to maintain.

For a table named Records with a column named Status, the structured-reference formula =COUNTA(Records[Status]) counts nonblank entries in that column. The table name and column heading in the formula must match the actual table. If the column includes formulas returning empty strings, the same COUNTA caveat still applies; a table reference does not change what the function counts.

When presenting a count, label what it means—for example, “cells containing a value,” “visible records,” or “completed statuses.” This small step prevents readers from confusing a count of all formula cells with a count of visible answers or finished records.

Frequently Asked Questions

Does COUNTA count a formula that returns an empty string?
Yes. The cell contains a formula, so COUNTA counts it even though the result looks blank. Use a LEN-based test if you want to count only results with visible characters.

Does COUNTA count zero?
Yes. Zero is a numeric value, not a blank cell. If a number format hides zero, COUNTA still counts the cell.

How do I count nonblank cells but ignore cells containing only spaces?
Use a TRIM-and-LEN approach on the relevant range, such as the formula shown above. It ignores ordinary spaces and can be wrapped in IFERROR when error cells should be ignored; note that TRIM does not remove every possible whitespace character.

How do I count only visible nonblank cells after filtering?
Use SUBTOTAL with function number 103 over the column and data rows you want to count. It excludes filtered-out rows and manually hidden rows.

Why does my count include cells that look empty?
They may contain formulas returning empty text, spaces, or another invisible character. They may also be part of a range that includes a heading or hidden rows. Inspect the cell contents and confirm that the formula range matches your intended data.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.