countblank but cell is not blank, has space

[lezawang]

Hi
I know =countblank() would count empty cells, but how to count empty cells (which look empty but has space char entered)?
Thank you so much.

 

[shaowu459]

=countif(h53:j61,"* *")

 

[lezawang]

Thanks, that works but it will also count cells which has text like this A1= "Good morning" , A2= "how are you"
I want the count to count cell which has " " once space or " " multiple spaces but no text (only spacebar entered)
Thank you.

=countif(h53:j61,"* *")

 

[iggy_]

Try it without the stars.
=countif(h53:j61," ")

That works only if you have a single space in the cell.

 

[ZVI]

=sumproduct(--(substitute(a1:a10," ","")=""))

 

[iggy_]

Sweet solution ZVI! that will count blank cells and cells with spaces. I guess if you just wanted cells with spaces would just subtract a Countblank(A1:A10) from your formula.

 

[ZVI]

Curiously COUNTBLANK counts not only the empty cells but also cells with empty string values (such as result of formula ="" or copy as value of that formula).
But ISBLANK for the latter returns FALSE – see A3 and B3.

Excel 2010 32 bit
	A	B	C
1	Data	ISBLANK	COUNTBLANK
2		TRUE	1
3		FALSE	1
Sheet2

Cell Formulas
Range	Formula
B2	=ISBLANK(A2)
B3	=ISBLANK(A3)
C2	=COUNTBLANK(A2)
C3	=COUNTBLANK(A3)
A3	=""

 

[iggy_]

Yea i just saw that a few minutes ago while looking at another question the OP posted. A single apostrophe in a cell (') will yield a similar result as =""

 

[Aladin Akyurek]

Hi
I know =countblank() would count empty cells, but how to count empty cells (which look empty but has space char entered)?
Thank you so much.

Count of a single space housing cells:

=COUNTIFS(A1:A10,"?",A1:A10," ")

Count of one or more space housing cells:

Either...
=SUMPRODUCT((TRIM(A1:A10)="")+0)-COUNTBLANK(A1:A10)

Or...
=SUMPRODUCT(--(SUBSTITUTE(A1:A10," ","")=""))-COUNTBLANK(A1:A10)