Sumproduct data on different sheets and multiple criteria

[TiaanvC]

I have a design sheet where I enter a quantity of fittings in the pipeline.
In a second sheet there is a standard table containing design adjustment factors for a wide variety of fittings. To calculate what the equivalent straight pipe length would be for the various fittings in the pipe section, I have to multiply the number of fitting (per size) with the respective adjustment factors and add it together.

At the moment I am using an elaborate Vlookup but as fitting types is added the maintenance of the formula is cumbersome.

I know the correct solution is SUMPRODUCT but I just cannot get the correct way to select the array from the EQ_factor sheet. I tried the following but it doesn't work

=SUMPRODUCT($B7:$J7,INDEX(EQ_factor!$B$3:$J$20,MATCH(EQ_factor!$A$3:$A$20,Design_Sheet!$A7,0)))

(see cell M7 where I tried this)

Pipe Des.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Nominal Diameter (mm)	90° Ellbow	45° Ellbow	Tee Run	Tee Branch	Globe Valve	Gate Valve	Angle Valve	Swing Check Valve	Butterfly Valve	User Defined (Reducers etc)	Straight Length (m)	Equivalent Length (m)
2
3	250	4					1					66	93.7
4	250	4					1					66	93.7
5	200	5					1					115	142.6
6	100			2								15	19.0
7	80	3										7.9	#N/A
Design_Sheet

Cell Formulas
Range	Formula
L3:L4	L3	=2.5+28.5+25+10
L5	L5	=11+30+64+10
M3:M6	M3	=L3+(B3*VLOOKUP($A3,EQ_factor!$A$3:$J$16,2,FALSE))+(C3*VLOOKUP($A3,EQ_factor!$A$3:$J$16,3,FALSE))+(D3*VLOOKUP($A3,EQ_factor!$A$3:$J$16,4,FALSE))+(E3*VLOOKUP($A3,EQ_factor!$A$3:$J$16,5,FALSE))+(F3*VLOOKUP($A3,EQ_factor!$A$3:$J$16,6,FALSE))+(G3*VLOOKUP($A3,EQ_factor!$A$3:$J$16,7,FALSE))+(H3*VLOOKUP($A3,EQ_factor!$A$3:$J$16,8,FALSE))+(I3*VLOOKUP($A3,EQ_factor!$A$3:$J$16,9,FALSE))+(J3*VLOOKUP($A3,EQ_factor!$A$3:$J$16,10,FALSE))
L7	L7	=0.4+3.8+3.7
M7	M7	=SUMPRODUCT($B7:$J7,INDEX(EQ_factor!$B$3:$J$20,MATCH(EQ_factor!$A$3:$A$20,Design_Sheet!$A7,0)))

Cells with Data Validation
Cell	Allow	Criteria
A3:A7	List	=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(XFC3&XFD3," ",""),".",""),"(",""),")",""))

The second sheet with the design adjustment factors:

Pipe Des.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Size	Le (m)
2		90° Ellbow	45° Ellbow	Tee Run	Tee Branch	Globe Valve	Gate Valve	Angle Valve	Swing Check Valve	Butterfly Valve
3	15	0.375	0.2	0.25	0.95	4.4	0.1	1.95	1.3
4	20	0.5	0.275	0.35	1.225	5.825	0.15	2.575	1.725
5	25	0.625	0.35	0.425	1.5	7.425	0.175	3.275	2.175
6	32	0.95	0.45	0.575	1.825	9.775	0.225	4.325	2.875
7	40	1	0.525	0.675	2.1	11.4	0.275	5.025	3.35
8	50	1.425	0.65	1	3	14.65	0.35	6.45	4.3	1.8
9	65	1.725	0.775	1.225	3.675	5	0.425	7.725	5.15	2.1
10	80	1.975	1	1.525	4.1	21.725	0.5	9.6	6.375	3.1
11	100	2.85	1.275	1.975	5.5	28.5	0.675	12.575	8.4	3.7
12	150	4.175	2	3.075	8.175	43	1	18.95	12.625	3.1
13	200	5.25	2.65	3.5	12.25	56.5	1.325	24.95	8.325	3.7
14	250	6.5	3.375	4.375	14.25	71	1.675	31.25	10.45	5.8
15	300	8	3.875	5	16.75	84.5	2	37.25	12.425	6.4
16	350	9.25	4.5	6.25	19.5	93	2.2	41	13.675
17	400	10.75	5	6.75	22	106.25	2.5	47	15.625
18	450	13.25	5.75	8	26.75	119.5	4.225	52.5	17.575
19	500	14.5	6.25	8.75	29.5	133.25	3.125	58.75	19.6
20	600	16.75	7.5	10.5	34.25	160.25	3.775	70.75	23.575
EQ_factor

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=SUMPRODUCT($B7:$J7,INDEX(EQ_factor!$B$3:$J$20,MATCH(A7,EQ_factor!$A$3:$A$20,0),))+L7

 

[TiaanvC]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=SUMPRODUCT($B7:$J7,INDEX(EQ_factor!$B$3:$J$20,MATCH(A7,EQ_factor!$A$3:$A$20,0),))+L7

Thanks for the simple solution. I updated my account details as well

 

[Fluff]

Glad to help & thanks for the feedback.
Thanks also for updating your profile.