Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Sep 2001
    Location
    Philadelphia suburbs
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    filter by length of cell contents (2000)

    Some accounts in column D have a length of 5, others have a length of 12. I would like to subtotal the accounts with a length of 5 separately, but I can't figure out how to filter by length. Can anyone help?

    Thanks very much,
    Sue

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: filter by length of cell contents (2000)

    Hi Sue..In a new column, say E, type =len(D1) and fill down the entire row of data. This will give the character lengths of the strings of text in each cell of column D. Sort Column E in Ascending order and you will now be able to subtotal for every change of value in column E.
    Jerry

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter by length of cell contents (2000)

    Jerry,

    Did you want to say =sumif(E1:E100,5,D1100) instead of countif to get the subtotal?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: filter by length of cell contents (2000)

    Yes I did, thanks <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>.....I will change the original post
    Jerry

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: filter by length of cell contents (2000)

    <P ID="edit" class=small>(Edited by Jezza on 23-Aug-05 22:35. Item in red now changed from countif...sorry Sue)</P>Sorry Sue

    In addition if you do not want to sort in ascending or descending you can create subtotals by adding this in a seperate field

    <span style="background-color: #FFFFFF;"><font color=red>=sumif(E1:E100,5,D1100) </font color=red></span hiwhite>

    this will count the number of 5's in column E...adjust the range to fit your data.
    Jerry

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter by length of cell contents (2000)

    and just for clarity, I should point out that in the sumif() formula, the D1100 represents whatever value it is that is desired to be summed.

    In Sue's example and in yours, column D contains the account numbers and is not likely what she is desiring to add together, so that was probably a bad example on my part. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: filter by length of cell contents (2000)

    Brett

    I reckon it must be account numbers with the varying lengths and the values for sub-totalling are in another column (just guessing). Your/our suggestion will suffice though as Sue can adjust the column to fit our example. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: filter by length of cell contents (2000)

    To atone for my earlier wrong posting, now deleted, you can also use the following array formula without intermediate fomulas:

    {=SUM(IF(LEN(E5:E105)=5,E5:E105,))}

    do not copy the {} curly parens, instead enter the formula using Ctrl-Shift-Enter. Edit E5:E105 to the correct range.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Toowoomba, Queensland, Australia
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter by length of cell contents (2000)

    Why not try using an autofilter and then use a custom filter to find all records of the form ?????. This will work for text fields but not (I think) on numeric. If the data is numeric, you could filter for records where the value is greater than 9999 and less than 100000.

  10. #10
    Lounger
    Join Date
    Sep 2001
    Location
    Philadelphia suburbs
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter by length of cell contents (2000)

    Thanks to all who responded to my question. I was able to get subtotals by sorting on length of the account field, but I was not able to make the SUMIF suggestions work. My account numbers are a numeric field, not text, because when I make the field text, it goes into scientific notation.
    My account numbers are in D242. The amounts that I would like to sum are in F2:F42. When I tried the formula=sumif(D242,5,F2:F42), i get a result of 0.
    When I try =SUM(IF(LEN(D242)=5,F2:F42)), I get #VALUE. Can anyone tell me what I am doing wrong.

    Thanks very much, Sue

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter by length of cell contents (2000)

    With =SUM(IF(LEN(D242)=5,F2:F42)) you need to make sure you press control-shift-enter after typing in the formula to make it an array formula.

    With the SUMIF, you have to follow Jezza's initial instructions of typing the formula in a blank column to show the length of the characters in each cell, then change the D242 in the formula to whatever column your LEN formula is in.

    HTH

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: filter by length of cell contents (2000)

    Sue,
    Did you enter the formula using Ctrl-Shift-Enter rather than just Enter?
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Lounger
    Join Date
    Sep 2001
    Location
    Philadelphia suburbs
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter by length of cell contents (2000)

    It helps enormously. Both ways worked perfectly.
    Thanks again to all who responded. This site is definitely the best for prompt and helpful assistance.

    Sue

Posting Permissions

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