Don’t trust Excel’s COUNTA function to count non-blank cells—here’s a better method

In most counting scenarios in Excel, COUNTBLANK reliably counts blank cells, and COUNTA reliably counts populated cells. However, with these functions comes a problematic paradox: an empty string from a formula is counted as “blank” by COUNTBLANK but as “populated” by COUNTA.

First, I’ll explain how each function works, then I’ll show you how I use the SUMPRODUCT function with LEN to overcome this problem.

HTG Wrapped 2025: 24 days of tech

24 days of our favorite hardware, gadgets, and tech

COUNTBLANK: Counting zero-length cells

The COUNTBLANK function counts all cells containing no characters. This includes:

• Truly empty cells
• Cells containing empty strings (“”) from formulas

So, it doesn’t count cells containing:

COUNTBLANK in action

Consider the range A2:A6, where cell A2 contains a letter, A3 contains a number, A4 is totally blank, A5 contains a single space, and A6 contains the result of a formula that returns an empty string.

When you apply the COUNTBLANK function to this range, only the cells with zero characters (A4 and A6) are counted. Even though cell A6 contains a formula, it produces a zero-character result, so COUNTBLANK includes it. And even though cell A5 looks empty, it contains a one-character space, so COUNTBLANK skips over it.

=COUNTBLANK(A2:A6)

Related

You Need to Know What Double Quotes Do in Excel

Double quotation marks aren’t just for speech.

COUNTA: Counting non-empty cells

On the other hand, COUNTA—the most inclusive count function in Excel—captures cells that aren’t completely blank, such as those containing:

• Numbers
• Text
• Errors
• Boolean values
• Spaces
• Empty strings from formulas

Note the last bullet in the list above: even if a formula returns an empty string, COUNTA counts it because the cell contains a formula. This behavior creates a conflict with COUNTBLANK, as they both count empty strings from formulas.

COUNTA in action

Let’s evaluate the same range as earlier, where cell A2 contains a letter, A3 contains a number, A4 is totally blank, A5 contains a single space, and A6 contains the result of a formula that returns an empty string.

Applying the COUNTA function to this range, the result is 4 because it counts cells A2 (the letter), A3 (the number), A5 (the space), and, crucially, A6 (the empty string from a formula, even though it returns zero characters).

=COUNTA(A2:A6)

The COUNTBLANK-COUNTA conflict (and how to fix it)

Here’s where many people often run into a major problem: COUNTBLANK and COUNTA both count empty strings from formulas.

This can be helpful with COUNTBLANK because it means that all cells with zero characters are counted, which is ideal in most scenarios. However, with COUNTA, this is problematic because it counts the cell as occupied (since it contains a formula), even though there’s no visible data. You could end up with an inflated total because you’re counting invisible formula results as if they were actual data points.

That’s why I use an alternative method to count non-blank cells.

Related

Stop using IFERROR and ISERROR in Excel: Use IFNA instead

Don’t fall into the trap of masking structural errors in your spreadsheet.

The fix: Use SUMPRODUCT with LEN to count cells based on their characters

Many online tutorials offer ways to overcome the problem of COUNTA counting empty strings from formulas, but none of them work as expected because they either behave in exactly the same way as COUNTA or use wildcards, which only work with letters, not numbers.

My hack is to use the SUMPRODUCT function with the LEN function to count only cells that have a content length greater than zero, thus filtering out the zero-length empty cells:

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

This formula explicitly checks if the length of the cell’s content is greater than zero. It is guaranteed to count only cells that have at least one character of content (including numbers, text, and spaces), completely ignoring any zero-length result. As a result, it counts only the text in cell A2, the number in cell A3, and the space in cell A5.

For consistency, you could replace the greater-than symbol in the formula with an equal sign (=) to count only the cells containing exactly zero cells (returning the same result as the COUNTBLANK function):

=SUMPRODUCT(--(LEN(A2:A6)=0))

Summary: Choosing the right counting function

Don’t get me wrong—I’m not telling you to avoid the COUNTA function altogether. It remains the fastest, easiest, and best way to count cells when you’re absolutely sure that your data range doesn’t contain any formulas returning an empty string.

However, understanding the difference between COUNTBLANK, COUNTA, and the SUMPRODUCT (LEN) fix means you can count cells with greater precision.

Here’s a summary of what you should use each function for:

Excel provides a full toolkit of counting functions beyond COUNTBLANK and COUNTA for virtually any counting scenario. COUNT only counts cells containing numbers, COUNTIF counts cells within a range that meet a single, specified condition, and COUNTIFS counts cells within a range that meet multiple criteria at the same time.

OS

Windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.