using COUNTA
 

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)

One way

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

--

HTH

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


"DougMc" wrote in message
...
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)

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

"Bob Phillips" wrote:

One way

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

--

HTH

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


"DougMc" wrote in message
...
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)

"DougMc" wrote ...
....
I am interested in understanding the purpose of --
preceeding the cell array? ...

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

You could simplify this to

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

--

HTH

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


"DougMc" wrote in message
...
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

"Bob Phillips" wrote:

One way

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

--

HTH

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


"DougMc" wrote in message
...
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)