AverageIfs with dates

[anwaee2]

I am running Excel For Mac 2010. I am having trouble getting monthly averages for specific months
of the year. I know the data columns are good as I am able to extract a lot of other information from them.
I would appreciate any help I can get with this. I am wondering if it is an Excel for Mac problem and not the formula.
Thanks in advance.

Dates is the named range for dates 1/1/2020 to 12/31/2020 (Data!A6:A371)
Named range Lo_2020 is in cell R2. (Data!C6:C371) (lowest temperature of each day)
Feb is in cell R3.

=AVERAGEIFS(R2,Dates,">="&R3,Dates,"<="&EOMONTH(R3,0))
Gives me #VALUE!

=AVERAGEIFS(Lo_2020,Dates,">="&Feb,Dates,"<="&EOMONTH(Feb,0))
Gives me #VALUE!

=AVERAGEIFS(Data!C6:C371,Data!A6:A371,">="&Feb,Data!A6:A371,"<="&EOMONTH(Feb,0))
Gives me #DIV/0!

 

[Z51]

Try this:

=AVERAGEIFS(INDIRECT(R2),Dates,">="&DATE(YEAR(INDEX(Dates,1)),MONTH(DATEVALUE(R3&"1")),1),Dates,"<="&EOMONTH(DATE(YEAR(INDEX(Dates,1)),MONTH(DATEVALUE(R3&"1")),1),0))

 

[Z51]

To shorten it a bit, you could put a helper formula in S3, the "DATE(YEAR..." portion, then replace that with S3 in the formula.

S3:

=DATE(YEAR(INDEX(Dates,1)),MONTH(DATEVALUE(R3&"1")),1)

New formula:

=AVERAGEIFS(INDIRECT(R2),Dates,">="&S3,Dates,"<="&EOMONTH(S3,0))

 

[anwaee2]

Try this:

=AVERAGEIFS(INDIRECT(R2),Dates,">="&DATE(YEAR(INDEX(Dates,1)),MONTH(DATEVALUE(R3&"1")),1),Dates,"<="&EOMONTH(DATE(YEAR(INDEX(Dates,1)),MONTH(DATEVALUE(R3&"1")),1),0))

Thank you so much, that works great.