INDEX MATCH inside of SUMSIF?

[Questionz]

Can someone please take a look at this sample doc. I put all the information on one sheet in this example but the 3 tables are separate worksheets. I'm trying to generate the numbers that are in yellow from the other two tables. I'm trying to find a way to use the raw date from sheet 1 and have the criteria range know that I'm talking about a specific week number.


The only thing I can think of (other than a macro) that might work is maybe for the criteria range2, putting in an index match so that it somehow knows how to convert the raw date into the correlating week number.


This is what I have so far: the ??? represents the criteria range2 where I'm supposed to put the dates from sheet 1.

=SUMIFS(C3:C10,A3:A10,A14,?????,B13)
=SUMIFS(C3:C10,A3:A10,A14,INDEX(B3:B10,MATCH(B13,G4:G11,0),MATCH(A14,A3:A10,0)),B13)

limitations:

I don't want to manipulate Sheet 1 (add any columns).
I don't want to use a macro unless this isn't possible via formulas.


Is there something wrong with my formula or Is there a better way to do this.

Any thoughts are appreciated.