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