AVERAGEIFS with multiple criteria on one range

[ponco]

Hi all,

I have an AVERAGEIFS statement that I need to take two different criteria on one range. when I try it, I get the good ol' #DIV/0! error.

The statement is as follows:

=AVERAGEIFS($B:$B;$C:$C;"MRO";$D:$D;"<25";$E:$E;"jakarta";$E:$E;"bali";$R:$R;">=01/01/2018";$R:$R;"<=31/01/2018")

It works fine if I only have one of the E column criteria (either one of them), but not with both. Can I not put multiple criteria on the same column range in an AVERAGEIFS? If not, could someone please suggest another way of doing this.

Thanks in advance!

 

[Peter_SSs]

The statement is as follows:

=AVERAGEIFS($B:$B;$C:$C;"MRO";$D:$D;"<25";$E:$E;"jakarta";$E:$E;"bali";$R:$R;">=01/01/2018";$R:$R;"<=31/01/2018")

Can I not put multiple criteria on the same column range in an AVERAGEIFS?

Not like that you can't. AVERAGEIFS evaluates all the conditions row by row and no row will meet both those conditions.

I would also avoid using whole column references where possible. I have used a small range for my sample below, but only make it big enough to be sure to cover your data or the calculation overhead will be significant.

=SUMPRODUCT(--(C2:C10="MRO"),--(D2:D10<25),(E2:E10="jakarta")+(E2:E10="bali"),--(R2:R10>=DATE(2018,1,1)),--(R2:R10<=DATE(2018,1,31)),B2:B10)/
SUMPRODUCT(--(C2:C10="MRO"),--(D2:D10<25),(E2:E10="jakarta")+(E2:E10="bali"),--(R2:R10>=DATE(2018,1,1)),--(R2:R10<=DATE(2018,1,31)))