# Sum values based on a cell with commas

##### New Member
Hi All,

I've had a browse and i can't seem to find this example. I've found examples of codes summing the numbers within the cell but nothing that i can find relating to finding the numbers within the cell seperated by either commas, slashes, etc - linking them back to a table or a list and summing numbers attached.

Basically;
Find all numbers in D2 (A1, A4, A5)
Sum relevant numbers in E2 (B2, B4, B5)

 A B C D E 1 100 Total 2 30 2,4,5 3 20 4 15 5 25

<tbody>
</tbody>

Unfortunately i've got a very limited VBA understanding, and i hope I've made it easy to understand where I'm getting at

Last edited:

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Find all numbers in D2 (A1, A4, A5)
How did you come about A1, A4 and A5?

For the second part, summing a group of values in 1 cell, I posted a solution in topic http://www.mrexcel.com/forum/excel-questions/858358-sum-values-based-cell-commas.html#post4171839:

Adjusted for your requirements, enter in E2 the following array formula (confirm with CTRL-SHIFT-ENTER, not just ENTER):
Code:
``=AVERAGE(IFERROR(1*MID(\$D2,(MID(","&\$D2,ROW(INDIRECT("1:"&1+LEN(\$D2))),1)=",")*ROW(INDIRECT("1:"&1+LEN(\$D2))),IFERROR(FIND(",",RIGHT(\$D2,1+LEN(\$D2)-ROW(INDIRECT("1:"&1+LEN(\$D2)))))-1,LEN(\$D2))),""))``

Last edited:
How did you come about A1, A4 and A5?

Sorry meant to be A2, A4, and A5

I was meant to add another column to represent the row # column

For the second part, summing a group of values in 1 cell, I posted a solution in topic http://www.mrexcel.com/forum/excel-questions/858358-sum-values-based-cell-commas.html#post4171839:

Adjusted for your requirements, enter in E2 the following array formula (confirm with CTRL-SHIFT-ENTER, not just ENTER):
Code:
``=AVERAGE(IFERROR(1*MID(\$D2,(MID(","&\$D2,ROW(INDIRECT("1:"&1+LEN(\$D2))),1)=",")*ROW(INDIRECT("1:"&1+LEN(\$D2))),IFERROR(FIND(",",RIGHT(\$D2,1+LEN(\$D2)-ROW(INDIRECT("1:"&1+LEN(\$D2)))))-1,LEN(\$D2))),""))``

Sorry Marcel I'm not following - it's coming up with the number '2'. I'd like it to find the numbers in D2 (2,4,5) which are situated in cells A2,A4,A5 - but sum those numbers which correspond with those found (B2,B4,B5 = 60)

Let me know if i'm doing it wrong

_______________________________

Edit; I understand the output of that formula - but I don't think it solves the initial question. As mentioned I've found threads which cover this, but my question has an addition

Last edited:
Apologies, I thought it was about summing the values in D2. I'll take another look.

The following array formula in E2 will do the trick, provided values in A1:A5 are sorted in ascending order.
Confirm the formula with CTRL-SHIFT-ENTER, not just enter.
Code:
``=SUM(IFERROR(LOOKUP(MATCH(1*MID(\$D2,(MID(","&\$D2,ROW(INDIRECT("1:"&1+LEN(\$D2))),1)=",")*ROW(INDIRECT("1:"&1+LEN(\$D2))),IFERROR(FIND(",",RIGHT(\$D2,1+LEN(\$D2)-ROW(INDIRECT("1:"&1+LEN(\$D2)))))-1,LEN(\$D2))),\$A\$1:\$A\$5,0),\$A\$1:\$A\$5,\$B\$1:\$B\$5),0))``
Remark: the sum of values in B2, B4 and B5 is 70, not 60.

 Row\Col A​ B​ C​ D​ E​ 1​ 1​ 100​ Total 2​ 2​ 30​ 2,4,5 70​ 3​ 3​ 20​ 4​ 4​ 15​ 5​ 5​ 25​

<tbody>
</tbody>

E2, control+shift+enter, not just enter:
Rich (BB code):
``````
=SUM(IF(ISNUMBER(FIND(","&A1:A5&",",","&D2&",")),B1:B5))
``````

Although the result is the same, I would prefer Aladin's solution

Non array solution :

=SUMPRODUCT(SUMIF(\$A\$1:\$A\$5,TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(D2,"."," "))&" "," ",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(TRIM(SUBSTITUTE(D2,"."," "))&" ")-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(D2,"."," "))&" "," ",""))))*99-99+1,99)),B1:B5))

Replies
2
Views
194
Replies
4
Views
269
Replies
14
Views
488
Replies
1
Views
481
Replies
3
Views
600

1,196,429
Messages
6,015,207
Members
441,882
Latest member
rcgyuk

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back