How to Count Non-Blank Cells in Excel: 5 Main Methods

I can also count across a row to see how complete each person's response is:

=COUNTA(B2:F2)

For Sara Ibrahim, this returns 5 because she filled in every field. For Nour Mahmoud, =COUNTA(B6:F6) returns just 1 since she only provided her email and abandoned the rest.

When COUNTA() is enough

Use COUNTA() when you need a simple count and your data is clean. If every blank cell in your range is genuinely empty (never touched, no formulas), COUNTA() works perfectly.

Where things get messy is when formulas return "" (empty strings). I'll explain that edge case in detail later in the "How Excel Treats Blank vs. Empty Cells" section.

Count Non-Blank Cells Using COUNTIF()

COUNTIF() gives you another way to count non-blank cells. Instead of counting everything that exists, it counts cells that match a condition. And that condition can be "not empty."

The "<>" criteria

Here's what the formula looks like:

=COUNTIF(B2:B13,"<>")

The "<>" part means "not equal to nothing," which is just another way to say "not blank." For the same email column from before, this gives me 10, same as COUNTA().

Why not use COUNTIF(range,"*")?

I see this in older tutorials, and it's a mistake. The wildcard "*" only counts text. It completely ignores numbers, dates, and TRUE/FALSE values. If your column has mixed data types, you'll get the wrong count.

Stick with "<>" instead. It works no matter what type of data you have.

When COUNTIF() is better than COUNTA()

The real advantage of COUNTIF() is that it leads naturally to COUNTIFS(). Once you need to count non-blank cells that also meet other conditions (like a specific department or date range), COUNTIFS() is right there waiting. If you think you might add conditions later, start with COUNTIF() so the switch is easy.

For most data, COUNTA() and COUNTIF(range,"<>") give identical results. Both count the same things, including errors and formula-generated blanks. The main difference is flexibility. COUNTIF() is your bridge to multi-criteria counting with COUNTIFS().

Count Non-Blank Cells That Meet Conditions

This is where COUNTIF() really starts to shine. COUNTIFS() lets you count non-blank cells that also meet other conditions.

COUNTIFS() with non-blank logic

Let's say I'm tracking employee training and I want to know how many people in Sales have submitted a score. Here's the formula:

=COUNTIFS(B2:B11,"Sales",E2:E11,"<>")

This means: "Count rows where Department is 'Sales' AND Score is not blank." In my tracker, this gives me 3 because Mona Sayed is in Sales but hasn't turned in a score yet.

Multiple conditions

You can stack up to 127 criteria pairs if you need to. Here's one that counts employees marked "Complete" who also have a score:

=COUNTIFS(C2:C11,"Complete",E2:E11,"<>")

This gives me 6 because Mona Sayed completed her training but never submitted a score.

Another example. Counting Engineering folks who have completion dates:

=COUNTIFS(B2:B11,"Engineering",D2:D11,"<>")

This returns 3 since Omar Mostafa's training is still pending and has no date yet.

The pattern is simple: use "<>" for the column you're checking for non-blank values, and put your specific filters in the other columns.

How Excel Treats "Blank" vs. "Empty" Cells

This is where most counting mistakes happen. Excel sees different types of "empty" that all look the same on screen.

Truly empty cells

A truly empty cell has never been touched or has been cleared completely. ISBLANK() returns TRUE, COUNTA() skips it, and COUNTBLANK() counts it. This is the only type of cell that all functions agree is blank.

Formula-generated empty strings

This one catches people all the time. When a formula like =IF(A1>60,"Pass","") returns "", the cell looks empty but actually contains a formula result. Here's how different functions see it:

• COUNTA() → Counts it (non-blank)
• COUNTIF(range,"<>") → Counts it (non-blank)
• COUNTBLANK() → Counts it (blank)
• ISBLANK() → Returns FALSE

See the problem? COUNTBLANK() says it's blank, but ISBLANK() says it's not. This trips up even people who've been using Excel for years.

The SUMPRODUCT() + LEN() workaround

To count only cells with actual visible content (no formula blanks, no spaces), use this:

=SUMPRODUCT(--(LEN(B5:B10)>0))

This checks how many characters each cell has. If LEN() returns 0, the cell is blank no matter what. The -- part converts TRUE/FALSE to 1/0, and SUMPRODUCT()` adds them up.

For an even stricter count that also ignores cells with spaces:

=SUMPRODUCT(--(LEN(TRIM(B5:B10))>0))

Cells with hidden characters

Sometimes a cell looks empty but has a space in it (CHAR(32)) or a non-breaking space (CHAR(160)) from web data or PDFs. COUNTA() counts it, LEN() returns 1 or more, and ISBLANK() says FALSE.

To catch these in your count, use the stricter formula I showed earlier: =SUMPRODUCT(--(LEN(TRIM(range))>0)). The TRIM() part removes regular spaces before counting. For non-breaking spaces, clean your data first with =SUBSTITUTE(A1,CHAR(160),"") or use the CLEAN() function.

Count Non-Blank Cells in Filtered Data

Here's something that catches people off guard: you filter your data, write a COUNTA() formula, and the result still includes the hidden rows. That's because COUNTA() doesn't care about filters. It counts everything in the range, whether you can see it or not.

SUBTOTAL(): The filter-aware alternative

SUBTOTAL() fixes this. It counts things while respecting filters.

=SUBTOTAL(3,D2:D11)

The 3 tells SUBTOTAL() to do a COUNTA()-style count. The difference: it skips rows that are hidden by filters.

Function numbers: 3 vs. 103

SUBTOTAL() has two modes:

• Function number 3: Skips rows hidden by AutoFilter, but still counts manually hidden rows.
• Function number 103: Skips ALL hidden rows (both filter and manual).

=SUBTOTAL(3,D2:D11)    -- Skips AutoFilter rows
=SUBTOTAL(103,D2:D11)  -- Skips ALL hidden rows

Most of the time, 3 is what you need. Use 103 only if you've manually hidden rows (right-click > Hide) and want those excluded too.

AGGREGATE(): An alternative for messy data

If your filtered data has error values in it, SUBTOTAL() will throw an error. AGGREGATE() handles this better:

=AGGREGATE(3,7,D2:D11)

The 3 is the function number (COUNTA()), and 7 tells it to ignore both hidden rows and errors. This is the safest option for messy data, but most people don't need it.

Counting Non-Blank Cells in Excel Tables

If your data is in an Excel Table (hit Insert > Table or press Ctrl+T), you can use structured references instead of cell addresses. This makes your formulas clearer and they update automatically when the table grows.

Structured references with COUNTA()

Instead of writing =COUNTA(C2:C9), you write:

=COUNTA(TaskTracker[Assigned To])

This counts non-blank cells in the Assigned To column of my TaskTracker table. The reference skips the header row automatically and expands when I add new rows.

Combining COUNTIFS() with structured references

COUNTIFS() works great with tables:

=COUNTIFS(TaskTracker[Status],"Done",TaskTracker[Completion Date],"<>")

This counts tasks marked "Done" that have a completion date. In my tracker, this gives me 4 since all completed tasks have dates.

Why tables are better for counting

Two things make tables useful. First, structured references skip the header row automatically, so you don't need to worry about starting from row 2. Second, when you add a new row to the table, every formula using structured references picks up the new data. No need to manually update ranges.

This is perfect for data that grows over time, like logs, trackers, or data you import regularly.

Common Problems When Counting Non-Blank Cells

Count is higher than expected

What happens: COUNTA() says 6, but you only see 4 cells with data.

Why: Two cells have formulas that return "". As I mentioned earlier, these look empty but technically aren't.

Fix: Use =SUMPRODUCT(--(LEN(range)>0)) instead of COUNTA(). This only counts cells with visible content, ignoring formula-generated blanks.

Hidden spaces mess up the count

What happens: A cell looks empty, but COUNTA() counts it and ISBLANK() says FALSE.

Why: The cell has spaces in it, probably from copying data from a website or another file.

Fix: Check with =LEN(A1). If it returns more than 0 when the cell looks empty, you've got hidden characters. Clean your data first with =TRIM(A1), or use the stricter formula I showed earlier: =SUMPRODUCT(--(LEN(TRIM(range))>0)).

Error values get counted when you don't want them

What happens: You want to count non-blank cells but errors like #DIV/0! or #N/A get included.

Why: Both COUNTA() and COUNTIF(range,"<>") treat errors as non-blank.

Fix: If your data also has filters, use =AGGREGATE(3,7,range) like I mentioned in the filtered data section. The 7 skips both hidden rows and errors. For non-filtered data, use =SUMPRODUCT(--(LEN(IFERROR(range,""))>0)).

Count doesn't update after filtering

What happens: You filter your data and COUNTA() still shows the unfiltered count.

Why: COUNTA() ignores filters completely.

Fix: Use =SUBTOTAL(3,range) as I covered in the filtered data section. It automatically adjusts based on what's visible.

Best Practices for Counting Non-Blank Cells

After dealing with these issues more times than I should admit, here's what I do now:

• Start with COUNTA() for clean, simple datasets. It's fast and straightforward.

• Use COUNTIF(range,"<>") if you might add conditions later. It connects naturally to COUNTIFS() for multi-criteria counting.

• Test for formula blanks before trusting the count. Spot-check a few "empty" cells with =LEN(cell). If you get anything other than 0, switch to the SUMPRODUCT()+LEN() approach.

• Use SUBTOTAL(3,range) for filtered data. It's the only counting function that respects filters.

• Clean imported data first. Run TRIM() and check for CHAR(160) on data from websites or other sources.

• Use Excel Tables for growing datasets. Structured references update automatically when you add rows.

• Document complex formulas. If you're using SUMPRODUCT(--(LEN(TRIM(range))>0)), add a comment explaining why. Future you will thank you.

Conclusion

Excel gives you several ways to count non-blank cells, and the right one depends on your situation. COUNTA() works for most cases. COUNTIF() and COUNTIFS() let you add conditions. SUBTOTAL() respects filters. And SUMPRODUCT() with LEN() is your backup when formulas create invisible blanks.

The trick isn't memorizing formulas. It's understanding what Excel thinks "blank" means. A formula returning "" looks empty but isn't. A space character is invisible but gets counted. Once you get these differences, you won't get surprised by wrong counts anymore.

If you want to keep building your Excel skills, check out our Data Analysis in Excel course. It covers formulas, pivot tables, and data cleaning that goes well with everything we covered here.