Hope it is a simple question, I have two columns, A contains dates, B contains numbers.

I want to average B:B but only if the date on A:A is the first of the month, for example.

So I thought I would do something like this =AVERAGEIFS(A:A,B:B,DAY=1) but sadly it doesn't seem to work this way.

CodePudding user response：

AVERAGEIFS does not allow the manipulation of the data as you would need.

You can use SUMPRODUCT:

=SUMPRODUCT(B1:B1000*(DAY(A1:A1000)=1))/SUMPRODUCT(--(DAY(A1:A1000)=1))

Or as Mayukh Bhattacharya stated:

=AVERAGE(IF(DAY(A1:A1000)=1,B1:B1000,""))

Depending on one's version this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

Note:

Both these formula will work quicker if the references are limited to the data set and not full columns references.