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

 

[jasonb75]

Here's one way that comes to mind.

=SUMPRODUCT(INDEX($F$3:$K$9,0,MATCH(C$12,$F$2:$K$2,0))*($E$3:$E$9=TRANSPOSE(IF($B$3:$B$9=$B13,$C$3:$C$9))))

 

[nathanthomson11]

Apologies @jasonb75, I posted the wrong screen capture. Attached is the correct one..

 

[jasonb75]

Attached is the correct one..

I don't see it

 

[nathanthomson11]

I don't see it

Let's try this again!

 

[jasonb75]

I'll give it some thought but I don't think that it's going to work with that layout. The dates in table 2 are not directly comparable to the year / week number headings in table 3. I suspect that there are to many details to cross reference in a single formula.

 

[jasonb75]

This works on my test, but may not work with your sheet due to a couple of unknown factors.
As you've cut off the grid references from the second image, I've had to assume the same ranges as the first one.
There is nothing in your posts to identify your method of week numbering so default has been used.

=SUMPRODUCT(IF(XLOOKUP($E$3:$E$9,$C$3:$C$9,$B$3:$B$9,"")=$B13,$G$3:$G$9),--(VALUE(YEAR($F$3:$F$9)&WEEKNUM(INDEX($F$3:$F$9,ROW($F$3:$F$9)-ROW($F$3)+1)))=C$12))

There may be other problems that I have not thought of.