# Perform calculations on COMMA SEPARATED VALUES in a single cell

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?

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