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
donaldb@281.com
"cottage6" <cottage6@discussions.microsoft.com> wrote in message
news:303CABD8-A293-4654-9AC6-168F2D06C003@microsoft.com...
> 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
donaldb@281.com
"cottage6" <cottage6@discussions.microsoft.com> wrote in message
news:8AACBF99-1203-4C5C-BD53-9F8C1E25236E@microsoft.com...
> 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
> donaldb@281.com
> "cottage6" <cottage6@discussions.microsoft.com> wrote in message
> news:8AACBF99-1203-4C5C-BD53-9F8C1E25236E@microsoft.com...
> > 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"
<cottage6@discussions.microsoft.com> 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