Forum Discussion
Counting cells containing spaces
I'm trying to count a column of cells which contain a string of numbers, dashes and spaces. I would like to count the cells which only contain spaces. I've tried: =COUNTIFS(C3:C10000,"*",C3:C10000,"><"), =COUNTIF(C3:C10000," "). I can't seem to find a formula just for this scenario.
Both formulae shall work if you'd like to count cells with one space " ". If to count cells with any number of spaces, e.g. " ", " ", etc
=COUNTIF(C3:C10000," *")If to count as well cells with empty strings "" and blank cells, when as suggested with SUMPRODUCT.
I though of countif with " *" but was concerned because that would also count any cell with text that starts with a space (e.g. " - - - "). I was also surprised you didn't submit a REGEXP solution. I really need to learn those expression rules better ....
You are right, " *" doesn't work in such case. As for regex, equivalent of SUMPRODUCT
=SUM(--REGEXTEST( C3:C10000, "^\s*$"))
maybe
=SUMPRODUCT(--(TRIM(C3:C10000)=""))
or if you need to exclude emply cells try:
=SUMPRODUCT((C3:C10000<>"")*(TRIM(C3:C10000)=""))
Does this do what you want?
=SUM(--(TRIM(C3:C10000)=""))
