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:
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:
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:
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:
References
Counting cells with text, ignoring spaces | MrExcel Message Board
Recommended articles
More resources
