count the blank cell

[mike]

I want to count the number of blank cell, but if I use
countif to count, e.g., countif(a1:a10, ""), the cell
with data " ", " ", etc. will be ignored. how to include
all the cell with space?

thanks.

 

[Ken Wright]

=SUMPRODUCT(--(TRIM(A1:A10)=""))

 

[CLR]

=SUM(COUNTBLANK(A1:A10),COUNTIF(A1:A10," "))

Vaya con Dios,
Chuck, CABGx3

 

[Alan Beban]

Well, interesting little problem. It seems to depend on how the " " etc.
got into the cells.

I don't believe either Ken Wright's nor CLR's suggestions are general
enough. Put the following in A1:A10--="",=" ",=" ",""," "," ", and 4
blanks--and experiment with their suggestions and the following

=SUM(COUNTIF(A1:A10,{"","""""","* *"}))

Alan Beban

 

[Harlan Grove]

Well, interesting little problem. It seems to depend on how the " " etc.
got into the cells.

I don't believe either Ken Wright's nor CLR's suggestions are general
enough. Put the following in A1:A10--="",=" ",=" ",""," "," ", and 4
blanks--and experiment with their suggestions and the following

=SUM(COUNTIF(A1:A10,{"","""""","* *"}))

Why should the middle criterion be included? A cell *evaluating* to two
double quotes won't appear blank. If it should be considered 'metablank', so
should LEN(X,0) and the like.

Also, wouldn't the last criterion happily include a cell containing the text

untested formulas in responses will bite you in the butt

To include cells containing any number of space characters as well as true
blanks, how about

=COUNTBLANK(A1:A10)+SUMPRODUCT((A1:A10<>"")*(TRIM(A1:A10)=""))

 

[Harlan Grove]

Under what circumstances does Harlan Grove's proposal return something
different from Ken Wright's--=SUMPRODUCT(--(TRIM(A1:A10)=""))?

Whitespace is useful. I believe you mean

=SUMPRODUCT(--(TRIM(A1:A10)=""))

You should be careful with '--' as it's now often used as a boolean to
numeric conversion operator.

To answer you, no cases I can think of. Silly of me to have assumed you had
found something wrong with Ken's formula.

With A1:A10 containing

<blank>
=""
=" "
=" "
=" "
'
""
" "
" "
" "

your formula returns 10: the first criterion matches A1, A2 and A6, the
second matches A7, and the third matches all the others. However, the 3rd
criterion matches *ANY* text that contains at least one space characters
along with *ANY* *OTHER* characters. Believing that fits any intelligible
definition of 'blank' is obtuse. That the 3rd criterion happens to produce
intended results is purely an accident due to the peculiar sample data to
which it has been applied. Replacing A9 and A10 with

now is the time
the quick brown fox

and your formula *still* returns 10.

Then there's the question why you believe double quotes themselves when they
appear as part of cell values shouldn't themselves be considered NONBLANK
characters that perforce render such cells nonblank. Since you see some need
to trek along this tangent, what would you do with cells containing nothing
but space characters and an *ODD* number of double quote characters? If you
wish to treat double quote characters like space characters, then change
them to space characters before trimming.

=SUMPRODUCT(--(TRIM(SUBSTITUTE(A1:A10,""""," "))=""))

With the changed A9:A10 as above, this at least returns 8 rather than 10.

But, again, silly of me to have believed your first response to CLR had any
point. I'll endeavor in future to distinguish your pointless responses from
those with a kernel of purpose.

 

[Alan Beban]

[snip} I'll endeavor in future to distinguish your pointless responses from
those with a kernel of purpose.

Suit yourself. It's not important.

Alan Beban