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?


Follow the steps:

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

Ribbon > Formulas > Defined Names > Define Name


Refers to:

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

Click OK


(2) Then enter the following formula in B2:


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.


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.