Perform calculations on COMMA SEPARATED VALUES in a single cell

We all know how to split comma separated values using delimiter. What if we need to sum, count or average the comma separated values?

Follow the steps:

(1) Select cell B2. With cell B2 selected, define the following name:

Ribbon > Formulas > Defined Names > Define Name

Name: MYARRAY

Refers to:

=EVALUATE(“{“&SUBSTITUTE($A2,” “,””)&”}”)

Click OK

(2) Then enter the following formula in B2:

=SUM(MYARRAY)

Note that relative reference is being used here, so the formula can be dragged down, if required.

(3) Similarly, you can perform COUNT and AVERAGE

=COUNT(MYARRAY)  [In column C in the example]

=AVERAGE(MYARRAY) [In column D in the example]

Let me know if this helps.