Ignoring Cells with Only Spaces

H

Heliocracy

I'm trying to use the COUNTA function, but I need it to ignore cells that
contain ONLY spaces. I don't want to ignore cells that merely begin or end
with a space, and I don't know how many spaces may be present in the cells I
do want to ignore. Can anyone please help?

Thanks,
Heliocracy
 
H

Heliocracy

That appears to do the opposite of what I'm asking.

If cell A1=" ", COUNTIF(A1,"* ") returns a 1. I need a formula to return a
zero if there are only spaces in cell A1.

Thanks.
 
R

Ron Coderre

Try something like this:

=SUMPRODUCT(--(LEN(TRIM(A1:A10))<>0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
H

Heliocracy

Yes, that does appear to do it. Thanks.

Ron Coderre said:
Try something like this:

=SUMPRODUCT(--(LEN(TRIM(A1:A10))<>0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP
Click to expand...
 
R

Ron Coderre

I'm glad that worked for you....and thanks for the feedback.

***********
Regards,
Ron

XL2002, WinXP
 
Top