Count cells with length not equal to 7

C

cottage6

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?
 
C

cottage6

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

Don Guillett

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

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?
Click to expand...


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
 
Top