Index/Match, Sum and Average

[juangregory]

Hello Excel gurus. This started out as an index/match project but I'm not sure if the best solution lies there. And anyway, I can't get it to work.
Objective: average 2 separate columns in a worksheet and return that average based on 2 criteria. Here is Sheet2:

Year	Month	Department	Actual	Budgeted
2021	10	5510	26.52	27.01
2021	11	5510	28.10	26.32
2021	12	5510	32	30.11
2022	01	5510	27.29	28.11
2022	02	5510	26.98	27.13

And my summary Sheet 1 would look like this:

	2022
Department	Productivity
5510	=average(32, 27.29, 26.98)/average(30.11, 28.11, 27.13) = 98.9%

The formula would have to match Sheet1!$B$2 against the Year array in Sheet2, and match Sheet1!$A2 against the Department array in Sheet2. Then calculate the averages for both Actual and Budgeted and divide them.

INDEX MATCH works great for finding individual months in Sheet2, but when I nest INDEX MATCH inside a SUM formula, (I was trying to keep it simple first to see if i could get totals, then average them later), i only get the first row's value of Budgeted. Either that or a #REF. I've been googling this for the last 3 hours and have tried any number of things (SUMIF, FILTER, and of course SUM(AVERAGE(INDEX(MATCH))) but so far i'm coming up empty.

 

[juangregory]

Actually my math is backward. 98.9 would be the quotient if i divided E by D.

 

[juangregory]

another correction: Year 2022 should be in the bottom three rows of Sheet2!A

 

[juangregory]

Issue resolved with =AVERAGEIFS. As usual, i was making things too complicated by half.