AVERAGEIFS using multiple ranges and multiple criteria

[LindsayS]

I have sorted through several posts on this topic, none of which I have had success with, I'm hoping someone can give me some clear direction on this one. I would like an average of a range of cells where multiple criteria must be met, including one range with two different criteria options.

To summarize:

Here is what I would like in cell J14:

- The average of cells D30:D213, IF D30:D213>0, IF P30:P213<=J10, IF Q30:Q213=0 OR >H10

I have had success with =AVERAGEIFS(D30:D213, D30:D213, ">0", P30:P213, "<="&J10, Q30:Q213, "=0"), but this formula does not include Q30:Q213>H10 criteria.


Thanks!

[Tony Valko]

Try this array formula**:

=AVERAGE(IF(D30:D213>0,IF(P30:P213<=J10,IF((Q30:Q213=0)+(Q30:Q213>H10),D30:D213))))

** 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.

[LindsayS]

Thank you! Your array formula works perfectly. The support is much appreciated!

[Tony Valko]

You're welcome. Thanks for the feedback!