Forum Discussion
Adding multiple values in a single cell to create a sum in an empty cell
Hello! I am trying to create a formula in which an empty cell returns the sum of values entered in the previous cell. For example, Cell S2 has "1.00 (alt+enter) 1.00 (alt+enter)" and I would like Cel...
Deleted
Try this:
Select T2.
On the Formulas tab of the ribbon, click Define Name.
Enter Total in the Name box, and in the Refers to box, enter the formula
=EVALUATE(SUBSTITUTE(LEFT(!S2,LEN(!S2)-(RIGHT(!S2)=CHAR(10))),CHAR(10),"+"))
Click OK.
Enter the formula =Total in T2.
This can be filled down if required.
Deleted
Try this one:
=SUM(1*TEXTSPLIT(S2,CHAR(10)))Hi Patrick2788. Thank you! I'm getting the #NAME? error.
- Which version of Excel are you using? It seems to be something other than 365.
- We are using Microsoft Office Standard 2016.
Deleted
This formula will pull up to 4 numbers and sum them. Another option might be FILTERXML but with no spilling there's going to be some messy extraction either way.
=IFERROR(LEFT(S2,FIND(CHAR(10),S2)-1)*1,0)+IFERROR(MID(S2,FIND(CHAR(10),S2)+1,FIND(CHAR(10),S2,FIND(CHAR(10),S2)+1)-(FIND(CHAR(10),S2)+1))*1,0)+IFERROR(MID(S2,FIND(CHAR(10),S2,FIND(CHAR(10),S2)+1)+1,FIND(CHAR(10),S2,1+FIND(CHAR(10),S2,FIND(CHAR(10),S2)+1))-(FIND(CHAR(10),S2,FIND(CHAR(10),S2)+1)+1)),0)+IFERROR(MID(S2,FIND(CHAR(10),S2,1+FIND(CHAR(10),S2,FIND(CHAR(10),S2)+1))+1,10)*1,0)