Summing a column based on the length of text in a cell in anothercolumn

[nughty]

Hi, I am trying to sum a column based on the text length of data in a
different column. I'm sure that I'm either not using the right
formula or I am missing some component of the one I am using. Anyway,
this is the formula:

=SUMIF(A1:A4,LEN(A1:A4)>4,E1:E4)

Here is visual data of what I am trying to do:
A B C D E
dshara 0 0 0 4
dsmsg 0 0 0 2
faut 0 0 1 3
fist 0 0 2 14

Basically, in this example, I want to come up with the total of 6.


Thanks, B.

 

[Mike H]

Try

=SUM(IF(LEN(A1:A4)>4,E1:E4))

'This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[Teethless mama]

=SUMPRODUCT(--(LEN(A1:A4)>4),E1:E4)

 

[nughty]

Try

=SUM(IF(LEN(A1:A4)>4,E1:E4))

'This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike







- Show quotedtext-

Thanks. I tried that but the result still shows 0. =(

 

[nughty]

=SUMPRODUCT(--(LEN(A1:A4)>4),E1:E4)







- Show quotedtext-

Mama, I tried this but it returned incorrect total. Does this just
count the number of cells that have text length of greater than 4?

 

[nughty]

Try

=SUM(IF(LEN(A1:A4)>4,E1:E4))

'This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike







- Show quotedtext-

Mike, I apologize, I tried the CTRL+Shift+Enter on my original
formula! My bad on that one. But when I did use the formula you
provided (and using the CTRL+Shift+Enter), it returned a incorrect
value. I have 78 rows of data (2-79, 1 is header row) and 22 columns
(A thru V). The V column data is the data that I am trying to sum if
the A cell text length is greater than 4 characters. One of rows has
3210 in the V column and the formula you provided returned 8. So I'm
not sure where this 8 is coming from (I have more than 8 A column
cells that have length greater than 4, too). So I still stand a
little confused on the formula!

 

[Mike H]

Hi,

Both of the solutions you have work and if they don't for you then I suspect
your data aren't what you think they are. Check your numbers are really
numbers and not text that look like numbers.

Mike

 

[David Biddulph]

You say "it returned incorrect total" but you had told us that the
correct total is 6, and 6 is the answer from the formula given. Which
"incorrect total" did you receive?

You ask:
"Does this just count the number of cells that have text length of
greater than 4?"
to which the answer is "No".
You asked for column E to be summed based on the criterion you gave,
and that is was TM's formula does. Did you not see the E1:E4 term in
the SUMPRODUCT? Did you leave that term out? If you did leave it
out, then you would indeed get an incorrect answer.

 

[nughty]

Hi,

Both of the solutions you have work and if they don't for you then I suspect
your data aren't what you think they are. Check your numbers are really
numbers and nottextthat look like numbers.

Mike






- Show quotedtext-

Mike,

Ok, I've tried formatting the A columns to General and Text and
formatting the numeric value cells to Number, General and Text. Still
the same value of 8. The data is just data that I copied/pasted from
a flat ASCII .TXT file. Is there something that I can do to verify
that my numbers are really numbers? A plain SUM function for the V
column produced the correct numbers, =SUM(V28:V47) is what I used. I
tried individual LENs on each of the A column cell values and they
produced the expected results, penf returned 4 and dcsoj returned 5.

 

[nughty]

You say "it returned incorrect total" but you had told us that the
correct total is 6, and 6 is the answer from the formula given.  Which
"incorrect total" did you receive?

You ask:
"Does this just count the number of cells that havetextlengthof
greater than 4?"
to which the answer is "No".
You asked forcolumnE to be summedbasedon the criterion you gave,
and that is was TM's formula does.  Did you not see the E1:E4 term in
the SUMPRODUCT?  Did you leave that term out?  If you did leave it
out, then you would indeed get an incorrect answer.
--
David Biddulph



- Show quotedtext-

David, in the example I gave 6 would be the number I would be looking
for. But when I apply the formula to my entire spreadsheet (which is
79 rows and 22 columns), it returned 8. For instance, A:38 has a
character length of 6 and its V column value is 3210 in the data, very
far from 8. The value I'm looking for from my entire spreadsheet
should be 3910. So there is a difference of 700 between the one
object and the other 20. Does this explain things a little?

 

[David Biddulph]

I suggest that you check the formula again (& double check that you've
used CSE to array-enter it). The formula gives 6, not 0.

 

[nughty]

I suggest that you check the formula again (& double check that you've
used CSE to array-enter it).  The formula gives 6, not 0.
--
David Biddulph



- Show quotedtext-

Thanks for the brain check reminder David, didn't change the E column
cell addresses to fit my data field. Changed it to V2:V79 instead of
E2:E79 and worked like a charm. Thanks everyone for your help.

 

[David Biddulph]

Changing the *format* of a cell won't change the *content* of the cell from
text to a number.
The way to tell whether A2 is text or a number is to look at =ISTEXT(A2) and
=ISNUMBER(A2).

If it is text, the possible ways that might change it to a number include:
Copy a blank cell, then select your text values and Edit/ Paste Special/ Add
Data/ Text to Columns
Look for spaces, non-breaking spaces [CHAR(160)], and other non-printing
characters, and get rid of them.

 

[David Biddulph]

No. What you have said doesn't explain things at all because you didn't
answer the question. You gave an example set of values, and we have a
formula that returns the value that you said you wanted. You haven't made
it clear what result (if different from the one you wanted) you got from
that formula and that data. Please tell us now, did you get 6 from that
formula and that data? If not, what value did you get?

If you get a correct answer with that data but an "incorrect" value from a
different data set, then it seems likely that the data set doesn't contain
the values that you think you've got. Perhaps the other discussion about
text values might help.