+ Reply to Thread
Results 1 to 7 of 7

Count cells with length not equal to 7

Hybrid View

  1. #1
    cottage6
    Guest

    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?

  2. #2
    Peo Sjoblom
    Guest

    RE: Count cells with length not equal to 7

    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?


  3. #3
    cottage6
    Guest

    RE: Count cells with length not equal to 7

    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?


  4. #4
    Don Guillett
    Guest

    Re: Count cells with length not equal to 7

    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?




  5. #5
    cottage6
    Guest

    Re: Count cells with length not equal to 7

    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?

    >
    >
    >


  6. #6
    Don Guillett
    Guest

    Re: Count cells with length not equal to 7

    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?




  7. #7
    Don S
    Guest

    Re: Count cells with length not equal to 7

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1