I have a column of dates for gas receipts and colums for Litres, Price/Litre, and Cost. I’d like to average the columns only if the date in the first column has a year of, say, 2016. So:
=AVERAGEIF(A2:A1000,YEAR()="2016",E2:E1000
I know this won’t work, but it gives an idea of what I want to do.
=AVERAGEIF(A2:A1000,"=2016"&YEAR(A2:A1000),E2:E1000)
is maybe closer? Is this even possible?
Hi
Yes, you were close: you can use AVERAGEIF and a formula… but here the formula should apply to the date range:
=AVERAGEIF(YEAR(A2:A1000); 2016;E2:E1000)
This is an array formula, so entered with Shift+Ctrl+Enter
Regards
Indeed, that’s neat 
Not with AVERAGEIF, because the criteria expression result is used as argument, but you can use =SUM((YEAR(A2:A1000)=2016)*E2:E1000)/SUM(YEAR(A2:A1000)=2016) entered as array/matrix formula (close with Shift+Ctrl+Enter instead of just Enter), or =SUMPRODUCT(YEAR(A2:A1000)=2016,E2:E1000)/SUMPRODUCT(YEAR(A2:A1000)=2016) where SUMPRODUCT already forces the arguments to arrays.
Thanks erAck. That works beautifully. I just wish I understood why 