SOLVED

Deleted
Not applicable

# 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!

10 Replies

# Re: Adding multiple values in a single cell to create a sum in an empty cell

@Deleted

Try this one:

=SUM(1*TEXTSPLIT(S2,CHAR(10)))

# Re: Adding multiple values in a single cell to create a sum in an empty cell

Hi @Patrick2788. Thank you! I'm getting the #NAME? error.

# Re: Adding multiple values in a single cell to create a sum in an empty cell

Which version of Excel are you using? It seems to be something other than 365.

# Re: Adding multiple values in a single cell to create a sum in an empty cell

We are using Microsoft Office Standard 2016.

# Re: Adding multiple values in a single cell to create a sum in an empty cell

@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)

# Re: Adding multiple values in a single cell to create a sum in an empty cell

It's doing something, but only returning 1.00 instead of 2.00. I really appreciate your help!

# Re: Adding multiple values in a single cell to create a sum in an empty cell

You might have more non-number characters in that cell besides the carriage return. You could use:
=LEN(S2)

This will determine the length of how many characters are in the cell.

best response
Solution

# Re: Adding multiple values in a single cell to create a sum in an empty cell

@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.

# Re: Adding multiple values in a single cell to create a sum in an empty cell

@Patrick2788 Thank you for your help! I appreciate you!

# Re: Adding multiple values in a single cell to create a sum in an empty cell

@Hans Vogelaar Thank you so much! That works perfectly. I really appreciate the help! -Shannon
1 best response

Accepted Solutions
best response
Solution

# Re: Adding multiple values in a single cell to create a sum in an empty cell

@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.