Averageifs based on month criteria

kreineke

New Member
Joined
Sep 4, 2014
Messages
7
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
 

Some videos you may like

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
Joined
Feb 26, 2002
Messages
2,318
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
Joined
Sep 4, 2014
Messages
7
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
Joined
Feb 26, 2002
Messages
2,318
It looks like I just mixed up your columns, which would explain a #DIV/0! error. Just swap those references.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,657
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top