Index with sumifs and averageifs

Hi,

I tried to make SUMIFS and AVERAGEIFS with INDEX.

For H14 of Sheet CO I made this formula and it turned out Value error.

=SUMIFS(INDEX(Sheet1!$S$2:$S$582,MATCH($A$7&'Q1'!G14,Sheet1!$D$2:$D$582&Sheet1!$K$2:$K$582,0)),"="&'Q1'!A7,INDEX(Sheet1!$S$2:$S$582,MATCH($A$7&'Q1'!G14,Sheet1!$D$2:$D$582&Sheet1!$K$2:$K$582,0)),"="&'Q1'!G14)



Cell H14(Sheet "CO") needs to SUM of matching Qty (Cell S9:S10 of "Sheet1"). Also, Cell I14(Sheet "CO") need to calculate AVERAGE of cost (Cell W9:W10 of "Sheet1").

So, Desired value for H14 is 2,000 and for I14 is 1.34 (=> 2144(Cell Y9 of Sheet1)+540 (Cell Y10 of Sheet1) / 1600 (Cell S9 of Sheet1) + 400 (Cell S10 of Sheet1)



Please help me.

WOW! It works I can't believe it can be solved so simply like what you answered. Thank you so much, daddaylonglegs!!!!