excel - Sum of 3 numbers in same cell using a reference formula as well

Question

IN B1 I have: 3,4,5 IN B2 I have the sum of these numbers 12

I would like to add these together and the sum to appear in B3

So, for B3, I need to tell excel to reference the cell holding the numbers, then add them together. This way, once I create the original formula, I can drag it through the remaining cells.

Reason is: The sum of these numbers (B2), are pulling the sum from a different location within the spreadsheet. I need to make sure these numbers match.

Thank you

Answer 1

Alternate solution (if exactly three numbers separated by a comma):

=SUMPRODUCT(--MID(SUBSTITUTE(B1,",",REPT(" ",99)),99*(ROW($1:$3)-1)+1,99))

Or, to make the formula more dynamic so that the B1 cell can have any quantity of numbers, as long as they are separated by a comma:

=SUMPRODUCT(--MID(SUBSTITUTE(B1,",",REPT(" ",99)),99*(ROW(INDIRECT("1:"&LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1))-1)+1,99))