Sum values based on a cell with commas

[haddy]

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

Haddy

 

[Momentman]

Find all numbers in D2 (A1, A4, A5)

How did you come about A1, A4 and A5?

 

[MarcelBeug]

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):

=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))),""))

 

[haddy]

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

 

[haddy]

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):

=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

 

[MarcelBeug]

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

 

[MarcelBeug]

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.

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

 

[Aladin Akyurek]

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:

=SUM(IF(ISNUMBER(FIND(","&A1:A5&",",","&D2&",")),B1:B5))

 

[MarcelBeug]

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

 

[azumi]

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))