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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > That's all what is asked... <g>
> >
> > Ken Wright wrote:
> > > =SUMPRODUCT(--(TRIM(A6:L26)<>""))
> > >
>
>
>