**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.

I tried to use the formula , but it is giving invalid. Please can you show the formula in excell sheet