Fórmula genérica
=SUMPRODUCT(expression,range)
Resumo
Para filtrar resultados de SUMPRODUCT com critérios específicos, você pode aplicar expressões lógicas simples diretamente a arrays na função, em vez de usar a função IF. No exemplo mostrado, as fórmulas em H5: H7 são:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
onde os seguintes intervalos nomeados são definidos:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Se você preferir evitar intervalos nomeados, use os intervalos acima inseridos como referências absolutas. As expressões lógicas em H6 e H7 podem ser combinadas, conforme explicado a seguir.
Explicação
Este exemplo ilustra um dos principais pontos fortes da função SUMPRODUCT - a capacidade de filtrar dados com expressões lógicas básicas em vez da função IF. Dentro de SUMPRODUCT, a primeira matriz é uma expressão lógica para filtrar pela cor "vermelha":
--(color="red")
Isso resulta em uma matriz ou valores TRUE FALSE, que são transformados em uns e zeros com a operação de duplo negativo (-). O resultado é esta matriz:
(1;0;1;0;0;0;1;0;0;0)
Observe que a matriz contém 10 valores, um para cada linha. Um indica uma linha onde a cor é "vermelha" e um zero indica uma linha com qualquer outra cor.
Em seguida, temos mais duas matrizes: uma para quantidade e outra para preço. Junto com os resultados da primeira matriz, temos:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Expandindo os arrays, temos:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),(10;6;14;9;11;10;8;9;11;10),(15;18;15;16;18;18;15;16;18;16))
O comportamento principal de SUMPRODUCT é multiplicar e, em seguida, somar arrays. Como estamos trabalhando com três arrays, podemos visualizar a operação conforme mostrado na tabela abaixo, onde a coluna de resultado é o resultado da multiplicação de array1 * array2 * array3 :
| array1 | array2 | array3 | resultado |
|---|---|---|---|
| 1 | 10 | 15 | 150 |
| 0 | 6 | 18 | 0 |
| 1 | 14 | 15 | 210 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 18 | 0 |
| 1 | 8 | 15 | 120 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 16 | 0 |
Observe que array1 funciona como um filtro - valores zero aqui "zeram" os valores nas linhas onde a cor não é "vermelho". Colocando os resultados de volta no SUMPRODUCT, temos:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Que retorna um resultado final de 480.
Adicionando critérios adicionais
Você pode estender os critérios adicionando outra expressão lógica. Por exemplo, para encontrar o total de vendas em que a cor é "Vermelho" e o estado é "TX", H6 contém:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Nota: SUMPRODUCT não faz distinção entre maiúsculas e minúsculas.
Simplificando com uma única matriz
Os profissionais do Excel geralmente simplificam um pouco a sintaxe dentro de SUMPRODUCT, multiplicando matrizes diretamente dentro de array1, desta forma:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Isso funciona porque a operação matemática (multiplicação) força automaticamente os valores VERDADEIRO e FALSO das duas primeiras expressões em uns e zeros.
