AVERAGEIFS Returning #DIV/0!

[LawNapier]

In the below AVERAGEIFS formula I am attempting to look for all rows for APPLES, PEARS and ORANGES in column A:A that have a value of RIPE in column B:B.

For the first two AVERAGEIFS for APPLES and PEARS, the average of column P:P works fine.

When I add the AVERAGEIFS for ORANGES, I get #DIV/0! as the result.

What I have determined is that the AVERAGEIFS for ORANGES does not find any matches and returns a value of "0" so that is screwing up the average calculation across APPLES, PEARS and ORANGES collectively.

How do I handle a "0" value so the formula ignores that value when returning the average result?


SAMPLE FORMULA
-------------------------------------

=SUM(

AVERAGEIFS(P:P,
A:A,"="&"APPLES"),
B:B,"="&"RIPE")

AVERAGEIFS(P:P,
A:A,"="&"PEARS"),
B:B,"="&"RIPE")

AVERAGEIFS(P:P,
A:A,"="&"ORANGES")
B:B,"="&"RIPE")

)

 

[jasonb75]

Use the method that FormR has shown you in your other thread.

 

[Joe4]

Please continue in your original thread here: AVERAGE HH:MM time using OR and AND together within the same formula

Please do not post the same question multiple times. All clarifications, directly related follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.