Count Cells That Are Not Blank in Excel

Updated 13 November 2026 · Standard formula, shown below

This article explains the different ways to count cells that are not blank in Microsoft Excel, covering formulas, built-in tools, and common pitfalls. You will learn the COUNTA function, the COUNTIF function, and how to handle formulas that return empty strings.

Why Counting Non-Blank Cells Matters

In data analysis, knowing how many cells contain data is essential. Whether you are tracking inventory, survey responses, or employee records, a simple count of non-empty cells helps you understand the completeness of your dataset. Blank cells can skew averages, totals, and other calculations, so identifying them is a first step in data cleaning.

The COUNTA Function

The most straightforward way to count non-blank cells is the COUNTA function. Its syntax is:

=COUNTA(value1, [value2], ...)

It counts cells that contain any type of information, including numbers, text, dates, errors, and even formulas that return an empty string (“”). For example, =COUNTA(A1:A10) returns the number of cells in the range A1:A10 that are not empty.

Note that COUNTA does not count cells that are truly blank, even if they have formatting applied. It also ignores cells containing only spaces (though spaces are technically text, so they are counted).

Using COUNTIF for More Control

If you need more flexibility, the COUNTIF function can count non-blank cells with a criteria. The formula:

=COUNTIF(range, "<>")

counts cells in the range that are not equal to nothing, i.e., non-blank. This is equivalent to COUNTA in most cases, but COUNTIF can be combined with other criteria. For instance, =COUNTIF(A1:A10, "<>"&"") does the same, but you can also add a second condition with COUNTIFS.

Dealing with Formulas That Return Empty Strings

A common issue is when a formula returns an empty string (“”) to make a cell look blank. For example, =IF(B1="", "", B1) returns “” if B1 is blank. The cell appears blank, but it contains a formula. COUNTA will count such cells as non-blank because they contain a formula. If you want to exclude these pseudo-blank cells, you need a different approach.

One method is to use SUMPRODUCT with LEN:

=SUMPRODUCT(--(LEN(A1:A10)>0))

This counts cells where the length of the text is greater than zero, ignoring empty strings. However, it also ignores cells with numbers (since LEN works on text), so it is not ideal for mixed data. A more robust formula is:

=SUMPRODUCT((A1:A10<>"")*1)

This counts cells that are not equal to an empty string, but it still counts cells with formulas that return “”. To exclude those, you can use SUMPRODUCT((A1:A10<>"")*(A1:A10<>"")) but that is redundant. The best way is to use COUNTIFS with a condition that checks for actual values:

=COUNTIFS(A1:A10,"<>",A1:A10,"<>"&"")

But this still counts formulas returning “”. The only reliable way to exclude them is to use a helper column or a VBA macro, but for most purposes, COUNTA is sufficient.

Using the Status Bar and Go To Special

For a quick visual count, you can select a range and look at the status bar at the bottom of Excel. It shows the count of non-empty cells (among other statistics) if you have the status bar configured. However, this is not a formula and does not update dynamically.

Another method is to use the Go To Special feature:

Select the range.
Press F5 (or Ctrl+G) and click Special.
Choose Constants or Formulas (depending on what you want to count) and uncheck all boxes except Numbers, Text, Logicals, and Errors.
Click OK. Excel selects all non-blank cells, and the status bar shows the count.

This is useful for a one-time count, but not for a dynamic formula.

Counting Non-Blank Cells in Filtered Lists

If you have a filtered list, COUNTA counts all non-blank cells in the range, including hidden rows. To count only visible non-blank cells, you can use the SUBTOTAL function:

=SUBTOTAL(103, A1:A10)

The function number 103 counts non-blank cells in visible rows only. This is useful when you filter data and want to see the count of visible entries.

Common Mistakes and Pitfalls

Counting spaces: Cells with spaces are not blank. COUNTA counts them. To avoid this, you can use =COUNTIF(A1:A10, "<>") but it still counts spaces. Use =SUMPRODUCT(--(TRIM(A1:A10)<>"")) to ignore spaces.
Counting formulas that return “”: As mentioned, these are counted by COUNTA. If you need to exclude them, use a helper column with a formula that checks if the cell is truly blank.
Mixing data types: COUNTA counts all types. If you need to count only numbers, use COUNT.
Range size: Ensure the range is correct. A common error is including extra rows or columns.

Frequently Asked Questions

What is the difference between COUNTA and COUNT?

COUNTA counts all non-empty cells, regardless of data type (numbers, text, errors). COUNT counts only cells containing numbers. Use COUNT when you need a numeric count.

How do I count cells that are not blank and also meet another condition?

Use COUNTIFS. For example, to count non-blank cells in A1:A10 where B1:B10 equals “Yes”, use =COUNTIFS(A1:A10,"<>",B1:B10,"Yes").

Does COUNTA count cells with formulas that return an empty string?

Yes, because the cell contains a formula, which is considered a value. To exclude them, you need a more complex formula or a helper column.

How do I count non-blank cells in a row?

Use =COUNTA(A1:Z1) to count non-blank cells in row 1 from columns A to Z.

Why does COUNTA return a higher number than expected?

Check for cells with spaces, formulas returning “”, or cells with apostrophes (which are text). Use =SUMPRODUCT(--(TRIM(A1:A10)<>"")) to ignore spaces.

Can I count non-blank cells across multiple sheets?

Yes, you can use a 3D reference, like =COUNTA(Sheet1:Sheet3!A1:A10), but it counts non-blank cells in all sheets. For more complex scenarios, consider a helper column.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.