Count cells with length not equal to 7
 

I want to count the number of cells in a column that do not have a value that
is 7 digits in length. I've tried several combinations of CountIf and Len
and have failed miserably! Can anyone help?

Try

=SUMPRODUCT(--(LEN(TRIM(A1:A10))<7))


Regards,

Peo Sjoblom


"cottage6" wrote:

I want to count the number of cells in a column that do not have a value that
is 7 digits in length. I've tried several combinations of CountIf and Len
and have failed miserably! Can anyone help?

try
=SUMPRODUCT((LEN(A2:A22)<7)*1)

--
Don Guillett
SalesAid Software

"cottage6" wrote in message
...
I want to count the number of cells in a column that do not have a value
that
is 7 digits in length. I've tried several combinations of CountIf and Len
and have failed miserably! Can anyone help?

Thanks to both Peo and Don whose formulas solved my problem. One additional
question; can I leave blank cells out of the count?

"Peo Sjoblom" wrote:

Try

=SUMPRODUCT(--(LEN(TRIM(A1:A10))<7))


Regards,

Peo Sjoblom


"cottage6" wrote:

I want to count the number of cells in a column that do not have a value that
is 7 digits in length. I've tried several combinations of CountIf and Len
and have failed miserably! Can anyone help?

sure. just add the
a1:a10<0 parameter
=SUMPRODUCT((LEN(TRIM(A1:A10))<7)*(a1:a10<))

--
Don Guillett
SalesAid Software

"cottage6" wrote in message
...
Thanks to both Peo and Don whose formulas solved my problem. One
additional
question; can I leave blank cells out of the count?

"Peo Sjoblom" wrote:

Try

=SUMPRODUCT(--(LEN(TRIM(A1:A10))<7))


Regards,

Peo Sjoblom


"cottage6" wrote:

I want to count the number of cells in a column that do not have a
value that
is 7 digits in length. I've tried several combinations of CountIf and
Len
and have failed miserably! Can anyone help?

Don, thanks a lot. I'm quite jealous of your logic!

"Don Guillett" wrote:

sure. just add the
a1:a10<0 parameter
=SUMPRODUCT((LEN(TRIM(A1:A10))<7)*(a1:a10<))

--
Don Guillett
SalesAid Software

"cottage6" wrote in message
...
Thanks to both Peo and Don whose formulas solved my problem. One
additional
question; can I leave blank cells out of the count?

"Peo Sjoblom" wrote:

Try

=SUMPRODUCT(--(LEN(TRIM(A1:A10))<7))


Regards,

Peo Sjoblom


"cottage6" wrote:

I want to count the number of cells in a column that do not have a
value that
is 7 digits in length. I've tried several combinations of CountIf and
Len
and have failed miserably! Can anyone help?

On Thu, 7 Apr 2005 08:17:13 -0700, "cottage6"
wrote:

I want to count the number of cells in a column that do not have a value that
is 7 digits in length. I've tried several combinations of CountIf and Len
and have failed miserably! Can anyone help?


Here's one way:

Assume your data starts in B1. Enter =IF(LEN(B1)<7,0,1)
in C1 & copy it down. Then Sum column C.

I'm sure you'll get several answers, some probably cleaner than this
one, however, it may get you on your way for now.

Don S