In this article:

How to Count Non-Empty Cells in Excel

May 8, 2024

You must be familiar with our tutorial for counting non-empty cells in Google Sheets. We now introduce our equivalent tutorial for Excel users. Scroll down to learn the three methods for counting non-empty cells in Excel.

COUNTA method

The COUNTA method is still applicable for Excel. For this tutorial, we will use the following data:

Sample Excel data

As you can see, there are certain cells in Column C that are blank. We want to count the number of cells on that column which contain data. The COUNTA function works well if the blank cells are really empty:

=COUNTA(C2:C11)

The result is shown below:

COUNTA function

SUMPRODUCT method for filtering white spaces

Oftentimes, stray white space can be found in cells that can inflate the results of the COUNTA function. A user named fairwinds on mrexcel.com forums provides the following solution:

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

The difference is shown below:

COUNTA and SUMPRODUCT compared

COUNTIF method for filtering special characters

Finally, you can encounter cases where the output contains cells riddled with special characters. The previous formulas will count them even if they only contain special characters. Fortunately, you can use COUNTIF to exclude those cells:

=COUNTIF(C2:C11,">0"&"*")

The result is shown below:

COUNTA, SUMPRODUCT, and COUNTIF functions compared

References

Counting cells with text, ignoring spaces | MrExcel Message Board

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->