using COUNTA

D

DougMc

How can I ignore a blank cell (spacebar pressed) when using COUNTA. I want
to count cells with valid data where a spacebar press in invalid. A blank
cell is valid (so is not counted)
 
B

Bob Phillips

One way

=SUMPRODUCT(--(A1:A1000<>" "),--(LEN(A1:A1000)>0))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

DougMc

Thanks for your help. I made a small modification to come up with

SUMPRODUCT(--(A1:A100<>" "),--(LEFT(A1:A100)>CHAR(32)))

This achieved what I wanted. (anything => than Char(32) is not included in
the count)

I am interested in understanding the purpose of -- preceeding the cell
array? Any advice would be appreciated. However I guess the main point is
it works!

Thanks
 
M

Max

...
....
I am interested in understanding the purpose of --
preceeding the cell array? ...
Click to expand...

It's to coerce the TRUE, FALSE returns in the arrays to 1's and 0's to
enable, in this instance, SUMPRODUCT's calculations to proceed further with.

Perhaps try also the 2* responses in this previous post:

http://tinyurl.com/64py9

*Bob Phillips' link to his page, and
Jason's example and explanation, with a nice touch on its evolution
 
B

Bob Phillips

You could simplify this to

=SUMPRODUCT(--(TRIM(A1:A100)<>""))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top