# Summing a List of Values

I can easily sum a comma-separated list of values with a formula like:

``````=SUM(11,13,17,23)
``````

I want to do the same thing with the list in a separate cell, so if A1 contains:

``````11,13,17,23
``````

I would like a single cell formula to return 64

Neither `=SUM(A1)` nor `=SUM(INDIRECT(A1))` work:

I can get an answer by using either TextToColumns or the UDF():

``````Option Explicit
Public Function zum(SIN As String) As Variant
Dim ary, a
ary = Split(SIN, ",")

For Each a In ary
zum = zum + Val(a)
Next a
End Function
``````

But I would rather use a single cell formula than VBA.

• Can we assume that the list in A1 is arbitrary length rather than all cases having four values? – fixer1234 Mar 26 '16 at 17:55
• Can we also assume that the numbers can be greater than 99 (i.e. not just two digits)? I'm thinking that some combination of the MID, RIGHT, and LEFT functions might work. – BillDOe Mar 26 '16 at 17:58
• If your question is: can a collection of arguments be specified externally as a string and interpreted as individual arguments, the answer is no. The only way Excel will interpret a delimited collection of values as individual arguments is if they are directly inside the function as an argument list. – fixer1234 Mar 26 '16 at 18:12

`=SUBSTITUTE(A1,",","+")`
Then Define Name `Result` and in Refers to write `=Evaluate(B1)`