Averageif function match month & year

[Bear]

Hi

Would you help to correct my averageif formual?

How can I get a average figures if the data fall in specific month & year? e.g. I wanna get the average sales if it falls in Oct 2018.

Thanks.

 

[vletm]

Bear
=AVERAGEIFS(B:B,A:A,">="&DATE(YEAR(D2),MONTH(D2),1),A:A,"<="&EOMONTH(D2,0))

 

[bosco_yip]

Or,

try this array formula in E2, confirm enter with CTRL+SHIFT+ENTER (CSE) instead of just enter :

=AVERAGE(IF(TEXT(A2:A330,"mmmyy")=TEXT(D2,"mmmyy"),B2:B330))

Regards
Bosco

 

[rahulshewale1]

hii @Bear,


try this one
Just enter...

=SUMIFS(B2:B3300,A2:A3300,">="&D2,A2:A3300,"<="&EOMONTH(D2,0))/COUNTIFS(A2:A3300,">="&D2,A2:A3300,"<="&EOMONTH(D2,0))


Regard
Rahul shewale

 

[Bear]

Bear
=AVERAGEIFS(B:B,A:A,">="&DATE(YEAR(D2),MONTH(D2),1),A:A,"<="&EOMONTH(D2,0))

Thanks vletm!
Your formual works perfectly.

 

[Bear]

Or,

try this array formula in E2, confirm enter with CTRL+SHIFT+ENTER (CSE) instead of just enter :

=AVERAGE(IF(TEXT(A2:A330,"mmmyy")=TEXT(D2,"mmmyy"),B2:B330))

Regards
Bosco

Thanks Bosco~
Thank you for your help. I learnt something new from you.