Averaging both within a month and weekday

[Brennen81]

Hello. I have searched everywhere and tried everything I know but cant seem to get this.

I have daily data over many years. I would like to average the all of one weekday in the same month. For example, All Mondays in November over the course of many years.

It is laid out like this:

...A............B.............C...................... BR..............BS
1 DATE.......11/02/09...11/03/09.............02/14/16.....02/15/16
2 VALUE......2.97.........3.45...................4.9.............1.9

So using the date row I would like to average the Value row for specific days in specific months.

Thanks so much!!!!!!!!!!!!

[Tony Valko]

Try an array formula** like this...

=AVERAGE(IF(WEEKDAY(B1:G1)=2,IF(MONTH(B1:G1)=11,B2:G2)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Where WEEKDAY 2 = Monday and MONTH 11 = November.

[Tony Valko]

Where WEEKDAY 2 = Monday

The weekday numbers are...

1 = Sun
2 = Mon
3 = Tue
4 = Wed
5 = Thu
6 = Fri
7 = Sat

[Brennen81]

That worked! Thank you Tony.

I also want to count the cells within the same criteria, so I tried changing it to COUNTIF and it doesn't seem to work. Any suggestions?

[Tony Valko]

For a count use...

=SUMPRODUCT(--(WEEKDAY(B1:G1)=2),--(MONTH(B1:G1)=11))