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>

 

[JazzSP8]

Re: How to Add up all Numbers in a Cell with a Line Break?

Hi

Does this work for you?

=SUM(A1:INDEX(A1:A100,MATCH(TRUE,(A1:A100=""),0)))

It's an array formula so you'll need to press CTRL, SHIFT and ENTER in the Formula bar (if all goes well the formula will be wrapped in { }).

HTH

 

[Willy Excel]

Re: How to Add up all Numbers in a Cell with a Line Break?

Thank you, Jazz. I tried your forumula and no luck. No error messages, but the totals are incorrect. Any other formula ideas?

 

[FormR]

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.

Hi, welcome to the forum!

Here is one option you could try:

Excel 2013
	A	B
2	2 1	3
3		0
4	1 2	3
Sheet1

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(0+(0&TRIM(MID(SUBSTITUTE(A2,CHAR(10),REPT(" ",99)),{1,2}*99-98,99))))

 

[Willy Excel]

Thank you, FormR. For the blank cells, it works. For the cells with one number, it works. However, for the cells with a line break, it has the #VALUE! error. Any ideas on how to solve that?

 

[FormR]

However, for the cells with a line break, it has the #VALUE! error. Any ideas on how to solve that?

Hi, it works for me as you can see in my screen shot. Maybe your line break is a different character to CHAR(10)?

If you put this formula in a spare cell and point it to a cell that has a line break after a single digit number what does it return?

=CODE(MID(A1,2,1))

 

[Willy Excel]

Pointing to a cell with a 1 over a 1, it returns 13.

 

[NoSparks]

formula works with the file you attached to your cross post at MSOfficeForums

 

[Willy Excel]

NoSparks, did you use yours on a PC? Mine is Mac.

 

[FormR]

Pointing to a cell with a 1 over a 1, it returns 13.

Hi, try changing as below:

=SUMPRODUCT(0+(0&TRIM(MID(SUBSTITUTE(A2,CHAR(13),REPT(" ",99)),{1,2}*99-98,99))))

Can you please post links here to your thread at the other forum?