I would like to count the number of cells in a column that are not blank or spaces. I'm familiar with the COUNTA function. But that won't work because some cells might have one or several spaces, and COUNTA will count them. I tried using the following:
=COUNTIF(A1:A100,"LEN(TRIM())>0") but that doesn't work because I have to specify a parameter for TRIM(). I realize I could also accomplish what I want by creating a helper column, say column B, with each cell equal to the TRIM() of the corresponding cell in column A, and using the following function to get the count:
=COUNTIF(B1:B100,"?*")But I was hoping to avoid using a helper column. I would be grateful for any suggestions.
1 Answer
You could use for example:
=SUMPRODUCT(--(TRIM(A1:A100)<>""))
