In this article:

How to Count Non-Empty Cells in Excel

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

Automate everything you track in spreadsheets with Lido
Learn more

Level up your Google Sheets skills with our free Google Sheets automation guide

Wasting too much time doing things manually in spreadsheets? Want to spend more time doing what you love? Our 100% free, 27-page Google Sheets automation guide is full of new tips and tricks that will save you time and money!