Sum Material Based on Different Criteria

[nathanthomson11]

Unfortunately the office restricts my ability to upload a file so I'll do my best to explain what I'm looking for based on the attached image.

What I am trying to do is sum in table #3 based on information from Table #1 & Table #2. For example, in C13, I want to sum all of "Ford" material in Table #2 under the matching Calendar week - in this case "202101". However, in order to know what material in Table #2 is "Ford", it also needs to reference Table #1 to see what material number belongs to "Ford". The total in C13 should be 6,000 - the sum of "10001" & "10005" under calendar week "202101".

I suspect it's either a SUMPRODUCT or INDEXMATCH but I can't find a proper combination.

 

[Peter_SSs]

.. or another option (assuming the previous results are correct )

nathanthomson11.xlsm
	B	C	D	E	F	G	H	I	J	K
1
2	Program	Material #		Material #	202101	202102	202103	202104	202105	202106
3	Ford	10001		98765	1000	1500	1000	1200	1400	1600
4	VW	10008		10005	5000	5500	5100	6000	7000	4000
5	GMC	10003		10003	3000	3300	3000	3500	4200	4300
6	GMC	10004		22222	1500	1700	1500	1500	2000	2100
7	VW	10006		10006	2700	2800	2700	2700	3200	3300
8	Ford	10005		10008	1300	1300	1500	1100	1600	2000
9	VW	10007		10007	6000	6100	6000	6000	6000	6000
10
11
12	Program	202101	202104	202103	202102	202105	202106
13	Ford	5000	6000	5100	5500	7000	4000
14	GMC	3000	3500	3000	3300	4200	4300
15	VW	10000	9800	10200	10200	10800	11300
Test6

Cell Formulas
Range	Formula
C13:H15	C13	=SUMPRODUCT($F$3:$K$9*($F$2:$K$2=C$12)*(XLOOKUP($E$3:$E$9,$C$3:$C$9,$B$3:$B$9,"")=$B13))

 

[nathanthomson11]

Let's try a change of approach then.

nathanthomson11.xlsm
	B	C	D	E	F	G	H	I	J	K
1
2	Program	Material #		Material #	202101	202102	202103	202104	202105	202106
3	Ford	10001		98765	1000	1500	1000	1200	1400	1600
4	VW	10008		10005	5000	5500	5100	6000	7000	4000
5	GMC	10003		10003	3000	3300	3000	3500	4200	4300
6	GMC	10004		22222	1500	1700	1500	1500	2000	2100
7	VW	10006		10006	2700	2800	2700	2700	3200	3300
8	Ford	10005		10008	1300	1300	1500	1100	1600	2000
9	VW	10007		10007	6000	6100	6000	6000	6000	6000
10
11
12	Program	202101	202104	202103	202102	202105	202106
13	Ford	5000	6000	5100	5500	7000	4000
14	GMC	3000	3500	3000	3300	4200	4300
15	VW	10000	9800	10200	10200	10800	11300
Test5

Cell Formulas
Range	Formula
C13:H15	C13	=SUM(FILTER(FILTER($F$3:$K$9,$F$2:$K$2=C$12),ISNUMBER(MATCH($E$3:$E$9,FILTER($C$3:$C$9,$B$3:$B$9=$B13),0))))

Thank you so much!!

 

[Peter_SSs]

Thank you so much!!

You're welcome. Glad we got there in the end.

 

[nathanthomson11]

@Peter_SSs - I'm hoping you can help me again. I have a very similar situation as the one you did for me above however slightly different.

What I am trying to do is sum in table #3 based on information from Table #1 & Table #2. For example, in C13 I want to sum the quantity of "Ford" product being build in that particular week which is displayed in Table 2 but shown in specific dates, not weeks. However, in order to know what material in Table #2 is "Ford", it also needs to reference Table #1 to see what material number belongs to "Ford". The total in C13 should be 1,000 - The Ford material is 10001 & 10005 but 10001 is built in cw18 (202118) and 10005 is built in cw19 (202119).

Apologies again that I am only able to provide a screenshot..