# AVERAGEIFS question by month/year

#### eduzs

##### Well-known Member
Hi,

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

 03/15/2014 10 03/20/2014 20 04/10/2014 30 04/18/2014 50 06/30/2014 30

<tbody>
</tbody>

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

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

Thanks.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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!

Just an alternative:

Excel 2010
ABCD
103/15/20141003/01/201415
203/20/20142004/01/201440
304/10/201430
404/18/201450
506/30/201430
Sheet1
Cell Formulas
RangeFormula
D1=AVERAGEIFS(B\$1:B\$5,A\$1:A\$5,">"&EOMONTH(C1,-1),A\$1:A\$5,"<="&EOMONTH(C1,0))

Thanks!!!

Replies
18
Views
267
Replies
0
Views
79
Replies
4
Views
280
Replies
11
Views
206
Replies
11
Views
236

1,196,046
Messages
6,013,076
Members
441,747
Latest member
darkman77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back