Looking for the best solution to returning the value (sum of) in table 2?

[GMW75]

Having difficulty on what formula to use in table 2 to sum the values from the variables entered in column 1. Not sure to use macros, vlookup, or another tool?
Table 1
[TABLE="width: 147"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]Process[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2.3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3.4[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table 2
[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]Process[/TD]
[TD]Product[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]1,2,5[/TD]
[TD]Door[/TD]
[TD]needs to lookup 1, 2, 5 and return the sum (5+3+3.4)[/TD]
[/TR]
[TR]
[TD]3,4[/TD]
[TD][/TD]
[TD]Needs to lookup 3 and 4 and return sum of (1.5+2.3)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

Excel 2010
	A	B	C	D	E
1	Process	Value
2	1	5		1,2,5	11.4
3	2	3		3,4	3.8
4	3	1.5		Total	15.2
5	4	2.3
6	5	3.4
5b

Cell Formulas
Range	Formula
E2	=SUM(SUMIF($A$2:$A$6,{1,2,5},$B$2:$B$6))
E3	=SUM(SUMIF($A$2:$A$6,{3,4},$B$2:$B$6))
E4	=SUM(E2:E3)

 

[Dave Patton]

Another alternative for your consideration.

Excel 2010
	A	B	C	D	E	F	G
1	Process	Value
2	1	5	1,2,5	11.4	11.4	11.4	1
3	2	3	3,4	3.8	3.8	3.8	2
4	3	1.5	Total	15.2			5
5	4	2.3
6	5	3.4					3
7							4
8
9					T10_1703c	5b
5b

Cell Formulas
Range	Formula
D2	=SUM(SUMIF($A$2:$A$6,{1,2,5},$B$2:$B$6))
D3	=SUM(SUMIF($A$2:$A$6,{3,4},$B$2:$B$6))
D4	=SUM(D2:D3)
E2	=SUMPRODUCT(SUMIF(A:A,G2:G4,B:B))
E3	=SUMPRODUCT(SUMIF(A:A,G6:G7,B:B))
F2	=SUMPRODUCT(SUMIF(A:A,{1;2;5},B:B))
F3	=SUMPRODUCT(SUMIF(A:A,{3;4},B:B))

 

[GMW75]

Those solutions would work if the repeat of the processes were standard but I have over 150 processes and each product which is a list of 3000 have a different process variation for each. To sum it up the products are on one sheet and the processes on another so when the product states the process series I need to return the sum of time for the products process. I am stumped as a simple vlookup does not work and not sure how to start to tackle the solution?

 

[Aladin Akyurek]

[TABLE="class: grid, width: 517"]
<tbody>[TR]
[TD]Process[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD][/TD]
[TD]Process[/TD]
[TD]Product[/TD]
[TD]Tvalue[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]1,2,5[/TD]
[TD]Door[/TD]
[TD="align: right"]11.4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]3,4[/TD]
[TD][/TD]
[TD="align: right"]3.8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In G2 control+shift+enter, not just enter, and copy down:

=SUM(IF(ISNUMBER(FIND(","&$A$2:$A$6&",",","&$E2&",")),$B$2:$B$6))

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G
1
2	1,2,5	1	2	5			11.4
3	3,4	3	4				3.8
4
5bb

Cell Formulas
Range	Formula
G2	=SUMPRODUCT(SUMIF('5b'!A:A,B2:F2,'5b'!B:B))
G3	=SUMPRODUCT(SUMIF('5b'!A:A,B3:F3,'5b'!B:B))

The name of my data sheet is 5b
The formula considers 5 possible process combinations
The process combinations (Column A) can be extracted to B:F

T10_1703c 5bb