Averageifs for multiple criteria

[dagreat1_87]

Hello everyone, was curious to see if you could assist. I'm trying to figure out how to calculate an average that includes multiple criteria from one column, while excluding multiple criteria from another column. i've figured out how to exclude multiple criteria but can't figure out how to INCLUDE multiple criteria. This is my current formula (I changed around the criteria names to make it less complex)....

=Averageifs('YTD Data'!AB:AB,'YTD Data'!AZ:AZ,"E",'YTD Data'!BC:BC, "<>apple", 'YTD Data'!BC:BC, "<>berry", 'YTD Data'!BC:BC, "<>crab",'YTD Data'!EF:EF,"3")

Essentially i need to figure out the averages of the scores in column AB, but only the ones that have E or F in column AZ that DO NOT have the words apple, berry, or crab in in column BC.

I hope this makes sense, and please let me know if you need me to expand on anything. Appreciate any help you can give.


Edit: added the attachment....test formula is =AVERAGEIFS(A:A,B:B,"E",C:C, "<>apple", C:C, "<>berry", C:C, "<>crab",D:D,"3").....how would i include "F" as something that needs to be a part of the average as well?

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[dagreat1_87]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

sorry about that. i've attached it

[wk9128]

Cell F2 formula

OR Array formula

[dagreat1_87]

Cell F2 formula

OR Array formula

hello, thanks for your help but these formulas give me the same result as the formula i was already using. In addition to "E", I also need "F" to be included in the criteria...with those filters, the result would be 91.5...I just can't get my formula to calculate it for me

[wk9128]

Cell C2 array formula