"Date between" formula with AVERAGEIF

[Bithsa]

Hi guys, my formula is below:

=AVERAGEIFS($G$2:G1000,$B$2:B1000,$B2,$D$2:D1000,"<="&MONTH($D2),$D$2:D1000,">="&DATE(D2,1,1)&"<="&DATE(D2,12,31))

The purpose is to calculate a ytd average for individual employees every month, and then the formula should be able to be applied to 2017 as well.

Criteria:
B=employee name
D=dates

The date written in cell D2 is 1/1/2016. The idea is that when 1/1/2017 occurs, the ytd average will reset itself and start over. I am currently getting the #DIV/0! error...can anybody figure out why?

Thanks!

 

[rilzniak]

I believe the mistake lies in the last criteria. Try this:

=AVERAGEIFS($G$2:G1000,$B$2:B1000,$B2,$D$2:D1000,"<="&MONTH($D2),$D$2:D1000,">="&DATE(YEAR(D2),1,1),$D$2:D1000,"<="&DATE(YEAR(D2),12,31))

 

[Jonmo1]

What is this criteria supposed to be doing ?

$D$2:D1000,"<="&MONTH($D2)

If D2 is a DATE, say 1/1/2016, then MONTH($D2) = 1, so that becomes
$D$2:D1000,"<="&1
$D$2:D1000,"<=1"

It's not possible for any value in column D to be Less then or equal to 1 AND Greater than or equal to DATE(YEAR(D2),1,1)

I think that criteria should just be removed.
=AVERAGEIFS($G$2:G1000,$B$2:B1000,$B2,$D$2:D1000,">="&DATE(YEAR(D2),1,1),$D$2:D1000,"<="&DATE(YEAR(D2),12,31))