add multiple numbers in one cell with those numbers remaining visi

[TD]

I'm trying to perform a function where I input multiple numbers into one cell
and have their sum appear in an adjacent cell. I will need to do this
repeatedly through many rows. Can someone please advise?! Many thanks.

 

[Don Guillett]

Pls restate your question with detail.
numbers separated by , or one number at a time to show a running total.

 

[Harlan Grove]

I'm trying to perform a function where I input multiple numbers into one cell
and have their sum appear in an adjacent cell. I will need to do this
repeatedly through many rows. Can someone please advise?! Many thanks.

Meaning cell C5 could contain a string like

1 2 4 17 -5 2 0 -7 23

and you want a formula in cell D5 returning the sum of the numeric
substrings, so 37, easiest to define a name like seq referring to the
formula

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))

and use it in the following array formula for cell D5.

D5 [array formula]:
=SUM(IF(MID(" "&TRIM(C5),seq,1)=" ",
--MID(TRIM(C5),seq,FIND(" ",TRIM(C5)&" ",seq)-seq)))

 

[Rick Rothstein]

Assuming Harlan's guess is correct (your cells could contain a string like 1
2 4 17 -5 2 0 -7 23 which you want to total up to get 37 as the answer)...
if you are able to use VB code (I say able because you may have to adjust
your security settings in order to be able to use VB code), there is a
simple User Defined Function (UDF) that can be implemented to do this. Press
Alt+F11 to get into the Visual Basic Editor, click Insert/Module on its menu
bar and then copy/paste the following into the code window that opened up...

Function SumCell(R As Range) As Double
SumCell = Evaluate(Replace(R.Value, " ", "+"))
End Function

Now, go back to a worksheet, put a space-delimited set of numbers (say 1 2 4
17 -5 2 0 -7 23) into a cell (say, C5), and put this in the cell you want
the sum to appear in...

=SumCell(C5)

and the formula should evaluate to 37. You can now use this formula like any
other putting the cell you want to sum between the parentheses.

 

[Harlan Grove]

Assuming Harlan's guess is correct (your cells could contain a string like 1
2 4 17 -5 2 0 -7 23 which you want to total up to get 37 as the answer)...
if you are able to use VB code . . .

....

If security isn't an issue, and the OP is (and/or his/her users are)
running Excel 2002 or later versions, then the OP could just use a
defined name calling an XLM function.

If the string of numeric substrings were in cell C5 and the sum of
those substrings were supposed to be in cell D5, with cell D5 the
active cell, define the name SUMNSS as the formula

=EVAL(SUBSTITUTE(TRIM(C5)," ","+"))

then enter the formula =SUMNSS in cell D5. This particular approach
won't trigger macro security warnings when opening the workbook.

If you're going to use VBA udfs, better to make the udfs general
rather than overly particular. In this case, a simple VBA wrapper
around the Excel OM's Evaluate would be the better approach.

Function evaludf(s As String): evaludf = Evaluate(s): End Function

Then try the formula

=evaludf(SUBSTITUTE(TRIM(C5)," ","+"))

 

[Rick Rothstein]

Small modification to my UDF (to account for the possibility of trailing
spaces in the series of numbers)...

Function SumCell(R As Range) As Double
SumCell = Evaluate(Replace(Trim(R.Value), " ", "+"))
End Function

 

[Rick Rothstein]

If you're going to use VBA udfs, better to make the udfs general

rather than overly particular. In this case, a simple VBA wrapper
around the Excel OM's Evaluate would be the better approach.

Function evaludf(s As String): evaludf = Evaluate(s): End Function

Then try the formula

=evaludf(SUBSTITUTE(TRIM(C5)," ","+"))

We can generalize my UDF and save the user from having to remember to pass
that combination of worksheet functions as an argument...

Function Calc(R As Range, Optional Operator As String = "+") As Double
Calc = Evaluate(Replace(WorksheetFunction.Trim(R.Value), " ", Operator))
End Function

where the user can now include the operator symbol to place between the
numbers in his/her series. So, for the general reader out there, the UDF can
be used to, say, multiply the values in the string with this function
call...

=Calc(A1,"*")

Just pass the operator symbol into the second argument. Note that the second
argument is optional and, if omitted, defaults to summation, so the OP's
originally requested summation would be performed by doing either this...

=Calc(A1,"+")

or, more simply, this...

=Calc(A1)

 

[Harlan Grove]

We can generalize my UDF and save the user from having to remember to pass
that combination of worksheet functions as an argument...

Function Calc(R As Range, Optional Operator As String = "+") As Double
  Calc = Evaluate(Replace(WorksheetFunction.Trim(R.Value), " ", Operator))
End Function

....

And in the off chance one wants the intersection of 2 ranges, one
would have to enter something like

=Calc("X:X 99:99","")

so a useless use of Replace in the udf.

More critically, why would you make the 1st arg a range rather than a
string?! To reduce its useful problem domain? You seem to have an
innate, subconscious resistance towards generality.