=AVERAGEIFS Incorporating an OR criteria

[lnapier]

In the below formula I need to incorporate some sort of OR function to average rows that reflect APPLE "AND" RED that are ROTTEN OR FRESH. The way I have it below adds two averages together, which doesn't come up with the correct result. Any ideas?
=
AVERAGEIFS(A:A,B:B,"="&"APPLE",C:C,"="&"RED",D:D,"="&"ROTTEN")+
AVERAGEIFS(A:A,B:B,"="&"APPLE",C:C,"="&"RED",D:D,"="&"FRESH")

 

[pgc01]

Hi

Try this array formula:

=AVERAGE(IF(B2:B1000="APPLE",IF(C2:C1000="RED",IF((D2:D1000="ROTTEN")+(D2:D1000="FRESH"),A2:A1000))))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

 

[VBA Geek]

a bit more messy formula can also be...

=IFERROR(SUMPRODUCT(IFERROR(($A$2:$A$10)/((($B$2:$B$10)="apple")*(($C$2:$C$10)="red")*((($D$2:$D$10)="rotten")+(($D$2:$D$10)="fresh")));""))/(SUMPRODUCT(((($B$2:$B$10)="apple")*(($C$2:$C$10)="red")*((($D$2:$D$10)="rotten")+(($D$2:$D$10)="fresh")))));0)

also requires CTRL SHIFT ENTER