Add up all Numbers in a Cell with a Line Break?

[Willy Excel]

Sum These	Place Here:
2 1	3
	0
1 2	3
2	2

<tbody>
</tbody>

Greetings! Is there a simple method to SUM all digits within a cell with line breaks?

Some cells in the column have no numbers, some have one number, and others have a line break with a number on top and number on bottom.

Or: is there a way to highlight the cells and show "Sum:" down bottom?

Thanks!<attachment></attachment>

 

[Willy Excel]

Thanks, Tony! I notice yours works if the cells have a number on top of a line break and a number on the bottom of a line break. It's returning "FALSE" if there's only one number in a cell or no numbers, however.

 

[TonyUK72]

Put bracket in wrong place......

Should be

=IF(LEN(I2)>=2,SUM(LEFT(I2,1),RIGHT(I2,1)),I2)

but this still doesn't work as it populates with a 0 in an empty cell

 

[Willy Excel]

Just tried this new one. Looks like it works to me! 0 in an empty cell is my goal for the project. Thank you, Sir.

 

[TonyUK72]

Just tried this new one. Looks like it works to me! 0 in an empty cell is my goal for the project. Thank you, Sir.

This formula will only work if ALL your values a 0 to 9 if any of your numbers have multiple digits we will have to rethink.

 

[Willy Excel]

Hmm...good to know. Although 10 or higher is rare, it is possible.

 

[Willy Excel]

Put bracket in wrong place......

Should be

=IF(LEN(I2)>=2,SUM(LEFT(I2,1),RIGHT(I2,1)),I2)

Looks like if there is a line break, it automatically treats it as having a length of greater than or equal to 2. Is that correct? And the "LEFT" will take from the top of a line break and "RIGHT" will take from the bottom of the line break. Is that correct?

 

[TonyUK72]

Sort of, we know that the length of combined characters will be 0, 1 or >2 irrespective of special characters. So the formula simply checks to see if the cell contains more than 2 characters. If it does it sums the 1st character on the left with the last character on the right. Otherwise it simply returns the cell value eg 0 for nothing or 1-9.

For multiple digit values i'm sure theres a way to check the type of character NUMBER or TEXT and actually have a variable length LEN statement.

 

[Willy Excel]

Sort of, we know that the length of combined characters will be 0, 1 or >2

Suppose it's a 1 on top and 3 on the bottom. I see we said >2 there. Since that's a total of two characters, but we said >2, I'm curious why the formula works?

 

[TonyUK72]

=IF(LEN(I2)>=2,SUM(LEFT(I2,1),RIGHT(I2,1)),I2)

The first thing this formula does is check the number of characters in the cell "LEN()" and says IF there are 2 or more characters we need to add them together
SUM() it's easy to find those characters because (I ASSUMED) they had a length of 1 so we take 1 character from the LEFT() and add it to 1 character from the RIGHT()
IF there is 1 or less character in the cell the formula simply presents that value as the answer.

 

[Willy Excel]

Genius! Never guessed that would have worked with a line break, but I guess it does. Thanks, Tony.

Also, I looked at the entire data set. Although possible, a number of 10 or more has never actually happened. The 1 in a 1000 or so that it does, I am willing to manually correct the error. For my purposes, this one is solved. Thank you to all!