microsoft excel – AVERAGEIF with INDEX and condition

Why does this return #VALUE! when I use AVERAGEIF?


When I use =SUM(INDEX((YEAR(A1:Z1)=A2)*(A10:Z10),1,0))
it works fine.


also works, but the problem is it counts all the zeros, that’s why I tried AVERAGEIF.

I would like to get the average using the INDEX function, not array formulas or SUMPRODUCT.