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 Cell T2 to return "2.00" after adding the figures in Cell S2. It is important to keep the format of S2 as close as possible, as they line up with another field's data.
We are using Microsoft Office Standard 2016, with Windows 10 Pro, Version 22H2.
Thank you very much!
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.
10 Replies
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.
