Excel - AverageIfs com vários critérios válidos em colunas diferentes

Question

Estou tentando obter o excel para me dar a média de vários objetos se eles atenderem a um critério. Estes são alguns dados simples que são semelhantes aos dados com os quais estou realmente trabalhando.

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

O que estou tentando agora é

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

O que eu quero é encontrar a média de linhas que são "Apple" e "Yes" ou "Pear" e "Yes". Ambos os pares são vãos pelo que estou fazendo. A resposta que estou esperando é 8 ((5 + 7 + 12) / 3). Em vez disso, o Excel me diz que estou dividindo por 0. Como devo corrigir minha fórmula para que ela me dê o que eu quero?

    

Answer 1

Você pode usar esta fórmula:

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

    

Answer 2

AVERAGIFS funciona somente quando a relação entre os critérios é OR, você pode usar aqui SUMPRODUCT , pois é mais flexível:

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