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

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

Please make sure that Name: MYARRAY
Refers to: =EVALUATE(“{“&SUBSTITUTE($A2,” “,””)&”}”)

Follow the steps properly, it should be sorted.

masters education

Thanks for a marvelous posting! I truly enjoyed reading it, you might be a great author. I will remember to bookmark your blog and may come back sometime soon. I want to encourage you to ultimately continue your great job, have a nice afternoon!

This is such a lovely solution. I have a tricky part though that is causing it not to work, I think.

The number values that are separated by commas, are not actually natively in the cell. Rather, they are alphanumeric codes that pull their value from a chart on another sheet via the VLOOKUP function. For example, I have a donation list and I have codes for each item a client donates. The value of each item is pulled from a table that shows the alphanumeric code for that item and the resulting monetary value.

For example:

BEDSET1, COMPSYS1 may show in a single cell.

Their values are $250 and $100, respectively.

These values are found on a table on Sheet 2.

I currently have a VLOOKUP function setup to pull the SINGLE value of each item if it’s just one item listed in the cell. But I need to list more than one item per cell to keep all of the donors data in a single row.

I’d like to be able to type the codes, separated by commas, in a single cell, and have a cell to the right show the total monetary value of those donations, without ever typing the actual monetary value in the cell. Is that possible?

Leave a Reply

You must be logged in to post a comment.