Counting nonblank and non white space cells
 

I have a user who wants a simple count of nonblank cells. But he wants the
count to skip cells that have a space (or two or more) in them as well. My
formula is

=COUNTA(G91:OFFSET(G118,-2,0))

I can't seem to apply trim in there anywhere. How can I give him what he
has requested?
Thanks.

Hi!

Try this:

=SUMPRODUCT(--(LEN(A1:A5)0),--(ISERROR(FIND(CHAR
(32),A1:A5))))

This will count all non-blank cells that do not have *ANY*
spaces, char(32), in them.

Biff

-----Original Message-----
I have a user who wants a simple count of nonblank
cells. But he wants the
count to skip cells that have a space (or two or more) in
them as well. My
formula is

=COUNTA(G91:OFFSET(G118,-2,0))

I can't seem to apply trim in there anywhere. How can I
give him what he
has requested?
Thanks.

.

B1:

=COUNTIF(A1:A10,"?*")+COUNT(A1:A10)

C1:

=B1-(B1-SUMPRODUCT(--(LEN(TRIM(A1:A10))0)))

C1 is the result cell.

Andrew wrote:
I have a user who wants a simple count of nonblank cells. But he wants the
count to skip cells that have a space (or two or more) in them as well. My
formula is

=COUNTA(G91:OFFSET(G118,-2,0))

I can't seem to apply trim in there anywhere. How can I give him what he
has requested?
Thanks.

=SUMPRODUCT(--(TRIM(A6:L26)<""))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Andrew" wrote in message
...
I have a user who wants a simple count of nonblank cells. But he wants
the
count to skip cells that have a space (or two or more) in them as well.
My
formula is

=COUNTA(G91:OFFSET(G118,-2,0))

I can't seem to apply trim in there anywhere. How can I give him what he
has requested?
Thanks.

That's all what is asked... <g

Ken Wright wrote:
=SUMPRODUCT(--(TRIM(A6:L26)<""))

LOL - That was my first thought, but I must have reread that question half a
dozen times before posting just because you had posted something different.
<vbg

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Aladin Akyurek" wrote in message
...
That's all what is asked... <g

Ken Wright wrote:
=SUMPRODUCT(--(TRIM(A6:L26)<""))

Thanks. It works!

"Ken Wright" wrote:

LOL - That was my first thought, but I must have reread that question half a
dozen times before posting just because you had posted something different.
<vbg

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Aladin Akyurek" wrote in message
...
That's all what is asked... <g

Ken Wright wrote:
=SUMPRODUCT(--(TRIM(A6:L26)<""))