Baixe a apostila de exemplo
Este tutorial irá demonstrar como calcular “sumproduct if”, retornando a soma dos produtos de arrays ou intervalos com base em critérios.
Função SUMPRODUCT
A função SUMPRODUCT é usada para multiplicar matrizes de números, somando a matriz resultante.
Para criar um “Sumproduct If”, usaremos a função SUMPRODUCT junto com a função IF em uma fórmula de matriz.
SUMPRODUTA SE
Combinando SUMPRODUCT e IF em uma fórmula de matriz, podemos essencialmente criar uma função “SUMPRODUCT IF” que funciona de forma semelhante à função integrada SUMIF. Vejamos um exemplo.
Temos uma lista de vendas realizadas por gerentes em diferentes regiões com as taxas de comissão correspondentes:
Suponha que sejamos solicitados a calcular o valor da comissão para cada gerente da seguinte forma:
Para fazer isso, podemos aninhar uma função IF com o Gerente como nossos critérios dentro da função SUMPRODUCT assim:
= SUMPRODUCT (IF (=, *))
= SUMPRODUTO (SE ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10))
Ao usar o Excel 2022 e anteriores, você deve inserir a fórmula pressionando CTRL + SHIFT + ENTER para obter as chaves ao redor da fórmula (veja a imagem superior).
Como funciona a fórmula?
A fórmula funciona avaliando cada célula em nosso intervalo de critérios como TRUE ou FALSE.
Calculando a comissão total para Olivia:
= SUMPRODUTO (SE ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10))
= SUMPRODUTO (SE ({VERDADEIRO; VERDADEIRO; FALSO; FALSO; FALSO; VERDADEIRO; FALSO; FALSO; FALSO}, {928,62; 668,22; 919,695; 447,384; 697,620; 480,564; 689,325; 752,366; 869,61}))
Em seguida, a função IF substitui cada valor por FALSE se sua condição não for atendida.
= SUMPRODUTO ({928,62; 668,22; FALSO; FALSO; FALSO; 480,564; FALSO; FALSO; FALSO})
Agora a função SUMPRODUCT ignora os valores FALSE e soma os valores restantes (2.077,40).
SUMPRODUCT IF com múltiplos critérios
Para usar SUMPRODUCT IF com vários critérios (semelhante a como funciona a função SUMIFS integrada), basta aninhar mais funções IF na função SUMPRODUCT assim:
= SUMPRODUCT (IF (=, IF (=, *))
(CTRL + SHIFT + ENTER)
= SUMPRODUTO (SE ($ B $ 2: $ B $ 10 = $ G2, SE ($ C $ 2: $ C $ 10 = $ H2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10)))
(CTRL + SHIFT + ENTER)
Outra abordagem para SUMPRODUCT IF
Freqüentemente, no Excel, existem várias maneiras de obter os resultados desejados. Uma maneira diferente de calcular "soma do produto se" é incluir os critérios dentro de a função SUMPRODUCT como uma matriz usando unário duplo assim:
= SUMPRODUTO (- ($ B $ 2: $ B $ 10 = $ G2), - ($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10)
Este método usa o unário duplo (-) para converter uma matriz TRUE FALSE em zeros e uns. SUMPRODUCT então multiplica os arrays de critérios convertidos juntos:
= SUMPRODUTO ({1; 1; 0; 0; 0; 1; 0; 0; 0}, {1; 0; 1; 0; 1; 0; 0; 0; 0; 0}, {928,62; 668,22; 919,695; 447,384; 697,620; 480,564; 689,325; 752,366; 869,61})
Dicas e truques:
- Sempre que possível, sempre bloqueie a referência (F4) de seus intervalos e entradas de fórmula para permitir o preenchimento automático.
- Se estiver usando o Excel 2022 ou mais recente, você pode inserir a fórmula sem Ctrl + Shift + Enter.
SUMPRODUTA SE no Google Sheets
A função SUMPRODUCT IF funciona exatamente da mesma forma no Planilhas Google e no Excel:
