Count cells with length not equal to 7

[Guest]

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?

 

[Guest]

Try

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


Regards,

Peo Sjoblom

 

[Don Guillett]

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

 

[Guest]

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

 

[Don Guillett]

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

 

[Guest]

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

 

[Don S]

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