Calc sum & avg of a column dependant on values contain in a cell.

Hello all,

I have a spreadsheet that am pulling my hair out on and could use some help if anyone had a little time as I am almost out of hair !!

I am looking for a non-VBA way to sum the contents of "i" cells from (Data 1/Column G- from attached spreadsheet) where "i" is the value contained in a cell ($D$1) and multiple this result by the average value of "j" cells from (Data 2/Column H) where "j" is the value contained in a cell ($E$1)
I have included sample data 1 / data 2 and manually calculated expected values (E5:E28).

I appreciate any help, advice or feedback.

Thank you in advance.

Jeff
non vba calculations.xlsx

Sumif * averageif

I looked at the sumif and averageif and cannot see how to make the number of cell to sum/average dependent on the value in the cells (D1 and E1).

If you could add a little formula to my attached spreadsheet I would be super appreciative.
Thanks in advance,
jeff

pic.JPG

To add a little clarity perhaps I added the arrows.
Thanks thanks

Solved....................

Ahh, clearly I misinterpreted the original question. Did you end up using INDIRECT or OFFSET for your solution?