Add (sum) numbers within cells

[Clint]

Using Excel 2007 with SP2, how can I enter and summarize multiple numbers
within one cell?
IE. 1+5+8+...=____
Or is this even possible?
Thanks for anything on this. Jim

 

[T. Valko]

One way:

A1: 1+5+8

=SUMPRODUCT(--MID(SUBSTITUTE(A1,"+",0),ROW(INDIRECT("1:" & LEN(A1))),1))

Result = 14

 

[Mike H]

Hi,

If you mean that you have 1+5+8 in A1, try this

=EVAL(A1)

Mike

 

[T. Valko]

=EVAL(A1)

I don't think there's a native EVAL worksheet function in Excel 2007.

 

[T. Valko]

=SUMPRODUCT(--MID(SUBSTITUTE(A1,"+",0),ROW(INDIRECT("1:" & LEN(A1))),1))

Caveat!

That formula only sums *the individual digits*. It won't work when you have
numbers like:

1+20+300+4000

The formula would return the sum of: 1+2+3+4 = 10

 

[Gord Dibben]

None that I know of.

Perhaps Mike meant to use a UDF like this.

Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function

=EvalCell(A1) where A1 contains 1+5+8


Gord Dibben MS Excel MVP

 

[T. Valko]

He may have been thinking of the Morefunc add-in function EVAL().

 

[Gord Dibben]

Most likely you're correct.

Laurent's does the same as the one I posted.


Gord