Averageifs for ranges with multiple columns

[andrew.beckett]

Hello,
Is there a way to take the average of the last 2 columns if the following criterion is met: Utility=BGE, Date is greater than or equal to 1/1/2020 and less than or equal to 1/31/2020?

I am trying to figure out how to take the average of 2 columns of data based on different criteria. I tried to use averageifs, but found you can only use it to average 1 column of data. I then tried to use the array formula =AVERAGE(IF((B3:B95>=B3)*(B3:B95>=EOMONTH(B3,0))*A3:A95="BGE",C3:D95)), but received the same #Value error.

I have attached the excel file below.

[wk9128]

G3 cell array formula

[XOR LX]

Hi,

You were just missing some parentheses in your formula:

=AVERAGE(IF((B3:B95>=B3)*(B3:B95>=EOMONTH(B3,0))*(A3:A95="BGE"),C3:D95))

You could also use:

=SUM(AVERAGEIFS(OFFSET(C:C,,{0,1}),B:B,">="&B3,B:B,">="&EOMONTH(B3,0),A:A,"BGE"))/2

which has the advantage that you can reference entire columns with little detriment to performance (which you certainly can't do with an array formula!), though the disadvantage that it's volatile. I'd stick with the array formula and keep the size of the ranges passed to a minimum.

Regards

[CARACALLA]

wrong formula

[andrew.beckett]

Thank you for your help!

[andrew.beckett]

Great, thank you!