AVERAGEIFS question by month/year

[eduzs]

Hi,

I need a formula to calculate the average value of a column given a month/year.

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]03/15/2014[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]03/20/2014[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]04/10/2014[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]04/18/2014[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]06/30/2014[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]

Averageifs moth = 3 and year 2014 = 10 + 20 + 30 / 3 = 20

Averageifs moth = 4 and year 2014 = 30 + 50 / 2 = 40

Thanks.

 

[Domenic]

Assuming that A2:B6 contains the data, try...

=AVERAGEIFS($B$2:$B$6,$A$2:$A$6,">="&DATE(2014,3,1),$A$2:$A$6,"<="&DATE(2014,3,31))

or

=AVERAGEIFS($B$2:$B$6,$A$2:$A$6,">="&DATE(E2,D2,1),$A$2:$A$6,"<="&DATE(E2,D2+1,0))

...where D2 contains the month, such as 3, and E2 contains the year, such as 2014.

Hope this helps!

 

[Andrew Poulsom]

Just an alternative:

Excel 2010
	A	B	C	D
1	03/15/2014	10	03/01/2014	15
2	03/20/2014	20	04/01/2014	40
3	04/10/2014	30
4	04/18/2014	50
5	06/30/2014	30
Sheet1

Cell Formulas
Range	Formula
D1	=AVERAGEIFS(B$1:B$5,A$1:A$5,">"&EOMONTH(C1,-1),A$1:A$5,"<="&EOMONTH(C1,0))

 

[eduzs]

Thanks!!!