Averageifs: Using multiple text criterias

[eg1400]

Hello,

I am trying to obtain the average of cells in a column. The cells in the column need to be averaged only if they contain "w_" or "wd_".

Below is the formula I am trying:
=AVERAGEIFS('All Data'!S:S, 'All Data'!B:B,"w_*", 'All Data'!B:B,"wd_*")

I keep getting a #DIV/0! error.

This formula works fine if I use only one of the criteria that the cells must contain.
Example: =AVERAGEIFS('All Data'!S:S, 'All Data'!B:B,"w_*")

I searched the forums for similar questions and found them, but none of their solutions seem to be working for me.

Thank you very much for your time!

 

[Eric W]

Try:

=SUM(SUMIFS('All Data'!S:S, 'All Data'!B:B,{"w_*","wd_*"}))/SUM(COUNTIFS('All Data'!B:B,{"w_*","wd_*"}))

 

[eg1400]

This appears to work! Thank you Eric!

 

[Eric W]

You're welcome!