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?

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]

 

Perform calculations on COMMA SEPARATED VALUES in a single cell

Let me know if this helps.

 
Comments

No comments yet.

Leave a Reply

You must be logged in to post a comment.