count blank

[John in Wembley]

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]

=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

 

[T. Valko]

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.

 

[John in Wembley]

I assume that's inclusive?

Try this:

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

That counts *all* characters.

Thank to the team