Excel Count If Not Blank: Formulas and Common Traps

Formulas & functions · Updated November 18, 2026

To count cells that are not blank in Excel, use =COUNTA(A2:A100), replacing the range with your data. If you need a condition or want to exclude cells whose formulas display an empty string, choose a formula that matches that specific meaning of “not blank.”

What “not blank” means in Excel

Excel distinguishes between an actually empty cell and a cell that contains something, even when that content is not visible. A cell may contain a number, text, a date, an error value, a space, or a formula. That distinction matters because different counting formulas interpret those cases differently.

For a straightforward count of occupied cells, COUNTA is usually the best starting point. It counts cells containing information of any type, including text, numbers, logical values, errors, and formulas that return an empty text string. It does not count genuinely empty cells. This makes it useful for a list where each entered value or formula should count as a filled cell.

However, “not blank” can mean something more specific: a cell has a visible value, it meets a condition, or its row remains visible after filtering. The right formula depends on which of those questions you are asking. Decide what should count before choosing a function.

Use COUNTA for a simple nonblank count

With entries in A2:A100, use =COUNTA(A2:A100). If A2:A100 contains 12 numbers or text entries, three genuinely empty cells, and one cell with an error, the result is 13: the error cell contains information and is included. A formula in the range that returns "" is also counted, even though the cell looks empty on the worksheet.

To apply this to your sheet, replace A2:A100 with the range you want to count. You can select the first and last cells by dragging across the range, or type the range directly into the formula. Using a defined range rather than an entire column can make the formula’s scope clearer and avoid counting unrelated content elsewhere in that column.

  • Use COUNTA when any entered content, including a formula or an error, should count.
  • Use a conditional counting function when only entries matching a rule should count.
  • Use a visible-row counting method when filtering or hiding rows changes what you want included.

One common misconception is that COUNTA counts only text. It does not: numbers and other nonempty cell contents count too. For a list containing names and numeric IDs, COUNTA counts both kinds of entry.

Count cells that meet a condition

COUNTIF counts cells in a range that match one criterion. With values in A2:A100, the formula =COUNTIF(A2:A100,"<>") uses the criterion “not equal to an empty value.” It is a handy choice when you are already applying criteria and want a compact nonblank count.

For example, if A2:A6 contains “Mia,” “Noah,” a genuinely empty cell, “Ava,” and a genuinely empty cell, the result is 3. The formula counts the three names and excludes the two empty cells. Text such as a single space is not the same as an empty cell, so a space-only entry may still be counted.

There is an important distinction when the range contains formulas that return "". A formula cell is not physically empty, and counting functions and criteria can treat that situation differently. If your intended rule is “count only cells whose evaluated value is not an empty string,” use an explicit comparison instead, as described in the next section. That makes your rule clearer than relying on an empty-cell criterion.

For multiple conditions, use COUNTIFS. With names or IDs in A2:A100 and status values in B2:B100, =COUNTIFS(A2:A100,"<>",B2:B100,"Open") counts rows where the first range is not blank and the corresponding status is Open. Each criteria range must be the same size and shape. If formula-generated empty strings are possible, check whether the criteria-based interpretation matches your intended rule before relying on the result.

Exclude formulas that display as blank

A formula can return an empty text string, commonly written as "". For example, a formula might leave a result cell visually empty until another cell has been filled in. COUNTA still counts that result cell because it contains a formula. This can make a count larger than the number of visible entries.

With results in A2:A20, use =SUMPRODUCT(--(A2:A20<>"")) to count cells whose evaluated values are not equal to an empty string. This comparison excludes both genuinely empty cells and formulas returning "". It includes values such as numbers, text, and spaces; a cell containing a space is not an empty string. The formula assumes the range does not contain errors, because an error in the comparison can make the result an error.

For instance, suppose A2:A6 contains “Paid,” a formula returning "", a genuinely empty cell, “Pending,” and the number 0. The comparison counts “Paid,” “Pending,” and 0, so the result is 3. Zero is a value, not a blank. If your business rule says that zero should not count, that is an additional condition—not a blank-cell test.

If error values occur in the range, decide whether they should count as occupied entries or be excluded. COUNTA includes errors. A comparison-based formula may return an error unless you explicitly handle those cells. It is better to define that policy than to silently mask errors that might signal a problem in the source data.

Spaces, hidden characters, and imported data

A cell that looks blank may contain one or more spaces, a nonbreaking space copied from a web page, or another invisible character. Excel treats that cell as containing text, so COUNTA can count it. A nonblank count therefore measures cell contents, not whether a person can see meaningful text.

For a quick check, select a suspicious cell and look at the formula bar. If it contains a space or other character, the cell is not truly empty. You can also test a single cell: assuming the suspicious value is in A2, =LEN(A2) returns the number of characters in its evaluated text. A result above zero suggests that something is present, though imported characters may not be obvious from the cell’s appearance.

When cleaning a text list, functions such as TRIM can remove ordinary extra spaces from text, but they do not remove every possible invisible character. Keep a copy of the original data before bulk cleanup, and inspect a few results to confirm the transformation is appropriate. Do not treat every space as junk automatically: in some datasets, whitespace or special characters may be meaningful.

Count visible entries after filtering

COUNTA counts cells in filtered-out rows as well as visible rows. If the question is “How many nonblank entries can I currently see?”, use SUBTOTAL instead. With data in A2:A100, =SUBTOTAL(103,A2:A100) counts nonempty cells in the visible rows and excludes rows removed by a filter. Function number 103 also ignores manually hidden rows.

The related function number 3 also counts nonempty cells while ignoring rows excluded by a filter, but it includes manually hidden rows. In other words, choose 103 when both filtered-out and manually hidden rows should be ignored; choose 3 when manually hidden rows should still count. As with COUNTA, a formula that returns "" may still count as a cell containing a formula. If those cells matter, test your exact layout and counting rule.

These formulas count cells in one specified range; they do not automatically tell you how many visible records exist if some records have blank cells in that particular column. For a reliable row count, choose a column that is consistently populated for every record, such as a unique ID, and count that column instead.

Choose the formula that matches your goal

For most worksheets, the choice is simpler when you describe the desired result in plain language first. “How many cells contain anything?” points to COUNTA. “How many visible entries remain after filtering?” points to SUBTOTAL. “How many evaluated values are not empty text?” points to an explicit comparison.

  • Count entered content, including formulas and errors: use COUNTA.
  • Count entries using a criterion: use COUNTIF; use COUNTIFS for multiple criteria.
  • Ignore formulas that return an empty string: compare the range with "", and account for errors if they are present.
  • Count only visible rows: use SUBTOTAL with function number 3 or 103, depending on how manually hidden rows should be treated.

When you are unsure, make a small test range with one genuine blank, one ordinary value, one space, one formula returning "", and, if relevant, an error. Compare the formula’s result with the entries you intended to count. This is often faster and safer than trying to infer how a complicated imported dataset will behave.

Common reasons the count looks wrong

The most frequent cause is a mismatch between what the formula counts and what the reader means by “filled.” A formula cell that displays nothing may still count with COUNTA. A space-only cell may count even though it looks blank. An error is also content for COUNTA, while the number 0 is a numeric value and should not be confused with an empty cell.

Another source of surprises is an oversized range. If a formula uses A:A, it checks the entire column, including any notes or helper values below the visible table. A bounded range such as A2:A100 is easier to audit. If the list grows regularly, consider using an Excel table so formulas can refer to a named table column and expand with the data.

Finally, check that the formula points to the intended column and starts on the correct row. When counting records, a column with optional notes or missing values may not represent the number of records. Use a consistently populated field, and verify the result against a small sample before using it in a report.

Frequently Asked Questions

What is the Excel formula to count cells that are not blank?
For a normal range, use =COUNTA(A2:A100), replacing the range with yours. It counts content of any type, including errors and formulas that return an empty string.

Does COUNTA count a formula that returns an empty string?
Yes. The cell contains a formula, even though the displayed result looks blank. If you want to exclude formula results equal to an empty string, use an explicit comparison such as =SUMPRODUCT(--(A2:A20<>"")), assuming the range has no errors.

How do I count nonblank cells with a condition?
Use COUNTIF for one condition and COUNTIFS for several. For example, a status column can be checked for “Open” while another range is checked for entries. Make sure the criteria ranges are the same size, and consider how formula-generated empty strings should be treated.

Why does Excel count a cell that looks empty?
It may contain a formula returning "", a space, or an invisible character. Select the cell and inspect the formula bar. The right fix depends on whether the cell should be cleared, cleaned, or excluded by the counting rule.

How do I count only nonblank cells that are visible after filtering?
Use =SUBTOTAL(103,A2:A100) for the specified range. It ignores filtered-out rows and manually hidden rows. Use function number 3 instead of 103 if manually hidden rows should remain included.

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.