Excel - AverageIfs с несколькими допустимыми критериями в разных столбцах

Question

Я пытаюсь получить Excel, чтобы получить среднее значение для нескольких объектов, если они соответствуют критериям. Это некоторые простые данные, которые похожи на данные, с которыми я на самом деле работаю.

    Fruit    Amount    Valid?
    Apple    5         Yes
    Apple    7         Yes
    Apple    10        No
    Orange   1         Yes
    Orange   9         No
    Pear     12        Yes
    Pear     7         No

То, что я сейчас пытаюсь

=AVERAGEIFS(B:B,A:A,"=Apple",A:A,"=Pear",C:C,"=Yes")

То, что я хочу, это найти среднее количество строк, которые являются либо "Apple" и "Да" или "Груша" и "Да". Обе пары vaild для того, что я делаю. Я ожидаю ответа 8 ((5+7+12)/3). Вместо этого Excel говорит мне, что я делю на 0. Как мне исправить мою формулу, чтобы она дала мне то, что я хочу?

Answer 1

AVERAGIFS работает только тогда, когда отношение между критериями равно ИЛИ, вы можете использовать здесь SUMPRODUCT как он более гибкий:

=SUMPRODUCT(B2:B8*((A2:A8="Apple")+(A2:A8="Pear"))*(C2:C8="Yes"))/SUMPRODUCT(((A2:A8="Apple")+(A2:A8="Pear"))*(C2:C8="Yes"))

Answer 2

Вы можете использовать эту формулу:

=SUM(SUMIFS(B:B,A:A,{"Apple","Pear"},C:C,"Yes"))/SUM(COUNTIFS(A:A,{"Apple","Pear"},C:C,"Yes"))