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?

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.

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>

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>

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.

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.

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.