Total values required for each week

Basically in my attachment, I need help as I have confused myself and need help of users who are more familiar with XL.

I need to produce a graph that has the total resources required for each week but the resources required change for each week as they are set for specific time frame.

Sorry hope this makes sense and somebody can help me out

In C2 i tried using

=SUMIF(Sheet1!$D$2:$D$5,Sheet2!A2,Sheet1!$Q$2:$Q$5)

but it just gives me the value for the required week and not the resources over the following weeks

Cheers

Thank you so so much