Averageifs based on month criteria

kreineke

New Member
OK,

I have been fighting a simple problem.

I want to average a range in column A if the cell contains a number(some cells are blank or show error), and the date in the adjacent cell is equal to a specified month.

Thanks for any help,

Kirk

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Asala42

Well-known Member
These array formulas will work.

If the dates fall within the same year you can use:
{=AVERAGE(IF(ISNUMBER(A1:A100),IF(MONTH(A1:A100)=MONTH(D4),B1:B100)))}

If they don't, you may want to include a year reference if you don't want your average to include last year's data for the same month.
{=AVERAGE(IF(ISNUMBER(A1:A100),IF((YEAR(A1:A100)=YEAR(D4))*(MONTH(A1:A100)=MONTH(D4)),B1:B100)))}

Where D4 contains a reference date.
Use Ctrl+Shift+Enter to get the brackets

kreineke

New Member
Please explain why to evaluate Month(a1:a100); that is the range that I am averaging. The dates are contained in the adjacent column B1:B100. I have tried this formula and it returns DIV#0

Asala42

Well-known Member
It looks like I just mixed up your columns, which would explain a #DIV/0! error. Just swap those references.

Thank you much

Replies
8
Views
107
Replies
3
Views
46
Replies
4
Views
93
Replies
4
Views
38
Replies
0
Views
46