Você não precisa aninhar nada. A função SUMPRODUCT é muito poderosa, permitindo que você conte ou some com base em condições muito complexas. Suporta os operadores lógicos AND e OR.
TL; versão DR
=SUMPRODUCT((($A$2:$A$10="Apple")+($A$2:$A$10="Pear"))*(($B$2:$B$10="Red")+($B$2:$B$10="Green"))*($C$2:$C$10<>"Yes"))
Versão longa (desculpe ...)
Você deve realmente aprender como usar essa função, porque ela é muito útil e pode economizar muito tempo. Aqui está um guia rápido e sujo sobre como usá-lo, mas eu aconselho você a manter algum tipo de arquivo de teste com exemplos simples, como o que você forneceu, para que você possa experimentá-lo. Não hesite em dividir os diferentes problemas que você está tentando resolver.
Basicamente, um SUMPRODUCT contém vários membros , cada um deles sendo uma condição. Se você multiplicar essas condições, obterá um AND lógico. Se você adicionar essas condições, obterá um OR lógico.
É muito mais fácil com um exemplo! Então, aqui está o detalhamento da fórmula final usando o exemplo de frutas.
Observação: para facilitar as coisas, usei os nomes de tabelas Fruits , o que permite o uso de intervalos nomeados e melhora a legibilidade.
Então,aquiestácomofunciona.SuaprimeiracondiçãoérealmenteumaORlógica(vocêquerMaçãsouPeras,jáqueumafrutanãopodeserambas.Entãoafórmula(1)tem2membros,ou2condições:Fruta=Maçã,Fruta=Pêra.
(Fruits[Fruit]="Apple") retornará TRUE se for uma maçã, FALSE caso contrário. Somando isso ao segundo membro ( (Fruits[Fruit]="Pear") ), você obterá uma matriz de 0 e 1. A lógica é a seguinte:
- Falso + Falso = 0
- Verdadeiro + Falso = 1
- Verdadeiro + verdadeiro = 1
Em seguida, o SUMPRODUCT soma todos os elementos dessa matriz (0 e 1). Qual é basicamente o mesmo que contar elementos. O resultado é 8, como esperado (célula D14).
A segunda condição (cor) funciona exatamente da mesma maneira. Se você quiser incluir uma terceira cor (por exemplo, "laranja"), basta adicionar um novo membro na equação com um + .
A 3ª condição é mais simples, você só tem 1 critério. Usei o sinal <> propositalmente para mostrar que você também pode excluir elementos. Na fórmula (1), você poderia ter usado por exemplo (Fruits[Fruit]<>"Banana") .
Importante: Por favor, note que em um SUMPRODUCT , quando há apenas um membro (como na 3ª equação), você precisa converter o resultado em um número. Lembre-se: o SUMPRODUCT cria uma matriz de TRUE e FALSE . Você pode fazer isso facilmente usando a função N(...) ou, alternativamente, escrevendo --(...) , que transformará TRUE em 1 e FALSE em 0.
Agora, essas três condições funcionam individualmente, mas queremos combiná-las em uma fórmula. E como queremos que cada uma dessas três condições sejam verdadeiras (frutas, cores e venenosas), precisamos criar um AND lógico. Isso pode ser feito da mesma maneira que fizemos no nosso OR, mas desta vez usaremos a multiplicação ( * ):
- Falso * Falso = 0
- Verdadeiro * Falso = 0
- Verdadeiro * Verdadeiro = 1
Para nossas três condições, simplesmente usamos as três fórmulas individuais que escrevemos anteriormente e as colocamos dentro de um SUMPRODUCT . Esses 3 membros devem ser encapsulados por parênteses e separados por um * (basicamente os multiplicamos).
Aqui estão as fórmulas finais, para você tentar:
=SUMPRODUCT((Fruits[Fruit]="Apple")+(Fruits[Fruit]="Pear"))
=SUMPRODUCT((Fruits[Color]="Red")+(Fruits[Color]="Green"))
=SUMPRODUCT(N(Fruits[Poisonous]<>"Yes"))
E o final:
=SUMPRODUCT(((Fruits[Fruit]="Apple")+(Fruits[Fruit]="Pear"))*((Fruits[Color]="Red")+(Fruits[Color]="Green"))*(Fruits[Poisonous]<>"Yes"))
Notas laterais:
- Cada condição que você testar (por exemplo, Cor = Vermelho) deve ser encapsulada entre parênteses:
(Fruits[Color]="Red") - A ordem dos parênteses é muito importante se você tiver condições OR. Por exemplo, a equação
(X and (Y or Z))não é igual a((X and Y) or Z). - Você pode usar os operadores clássicos para testar uma condição: = para igual, < > para diferentes, > e < para maior / menor que, > = e < = para maior / menor ou igual.
- Usamos o
SUMPRODUCTpara contar, mas também podemos usá-lo para somar as coisas. Se um dos membros da equação não tiver um sinal=, os valores serão considerados (veja o exemplo abaixo, onde a coluna G é somada). - Os critérios
"Apple"podem ser substituídos por uma referência a uma célula, que pode ser um menu suspenso. É uma prática melhor usar variáveis, em vez de escrever texto diretamente dentro de uma fórmula. -
SUMPRODUCTpode ser uma fórmula de uso intensivo de recursos, já que está fazendo multiplicações e somas ... Dependendo de quantas condições você está testando, de quão grande é o conjunto de dados e de quantas vezes você está usando umSUMPRODUCT. Para condições mais simples,SUM.IFSé provavelmente mais rápido. -
SUMPRODUCTtambém suporta pesquisa de texto parcial, conforme mostrado abaixo:
