1

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:

enter image description here

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
3

You need a helper cell where you write the formula in B1 for example

=SUBSTITUTE(A1,",","+")
Then Define Name Result and in Refers to write =Evaluate(B1)
and where you want the Sum write =Result

|improve this answer|||||
  • I am shocked!! ................the Jurassic =Evaluate function is STILL lurking around! – Gary's Student Mar 26 '16 at 18:32
  • I am marking this as ANSWERED since only a single helper cell is required and the trick can be copied down a column – Gary's Student Mar 26 '16 at 18:35
  • You can try to combine in Refers to =Evaluate(Substitute(A1,",","+")) in the Define Name – user555689 Mar 26 '16 at 18:38
  • You are correct! .................the helper cell can be avoided! – Gary's Student Mar 26 '16 at 18:58
  • Very clever workaround. +1 – fixer1234 Mar 26 '16 at 22:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.