hi team

counta & countblank only works if the cell is cleared with 'clear
contents'. Rather then educate all the staff can I do something else?
The other issue is these cells are linked to another sheet.
how about count all the cells with strings between 4 & 10 in lenth as
the cells will contain patients names - thats what I wish to count.

how can I count cells based on them containing a text string of say
between 4 and 10 letters in lenght?

cheers

=SUMPRODUCT(--(LEN(A1:A10)>=4),--(LEN(A1:A10)<=10))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

John in Wembley wrote:
>
> hi team
>
> counta & countblank only works if the cell is cleared with 'clear
> contents'. Rather then educate all the staff can I do something else?
> The other issue is these cells are linked to another sheet.
> how about count all the cells with strings between 4 & 10 in lenth as
> the cells will contain patients names - thats what I wish to count.
>
> how can I count cells based on them containing a text string of say
> between 4 and 10 letters in lenght?
>
> cheers

--

Dave Peterson

> between 4 and 10 letters in lenght?

I assume that's inclusive?

Try this:

=SUMPRODUCT(--(LEN(A1:A10)>=4),--(LEN(A1:A10)<=10))

That counts *all* characters.

--
Biff
Microsoft Excel MVP


"John in Wembley" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> hi team
>
> counta & countblank only works if the cell is cleared with 'clear
> contents'. Rather then educate all the staff can I do something else?
> The other issue is these cells are linked to another sheet.
> how about count all the cells with strings between 4 & 10 in lenth as
> the cells will contain patients names - thats what I wish to count.
>
> how can I count cells based on them containing a text string of say
> between 4 and 10 letters in lenght?
>
> cheers
>

On Fri, 7 Sep 2007 14:22:49 -0400, "T. Valko" <(E-Mail Removed)>
wrote:

>> between 4 and 10 letters in lenght?
>
>I assume that's inclusive?
>
>Try this:
>
>=SUMPRODUCT(--(LEN(A1:A10)>=4),--(LEN(A1:A10)<=10))
>
>That counts *all* characters.

Thank to the team