How to average vallues across multiple columns based on a date range?

Hi all,

I want to average values across multiple columns based on a giver date in the same row.
i have tried using
=AVERAGEIFS(C2:G7,B:B,">31/1/19",B:B,"<1/3/2019")
but it returns a #VALUE! error

I have included a sample book containing my values and formulae.

Will appreciate any help. Thank you!

All of the ranges used in AVERAGEIFS, or similar functions must contain an equal number of rows and columns.

You will need an array formula for what you are trying to do. Where the dates used in your formula are located in the cells I4 and I5.

=AVERAGE(IF((B2:B7>I4)*(B2:B7<I5),C2:G7))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.