Excel: SOMARPRODUTO Condicional

O Excel tem a função SOMARPRODUTO, que de forma conveniente retorna a soma dos produtos de intervalos (ou matrizes), ou seja, multiplica posição a posição os fatores de cada intervalo (valor na posição i de cada intervalo) e soma os produtos resultantes.

Vou mostrar um exemplo prático de SOMARPRODUTO extraído do artigo em Blog dos Cursos, por André Victor, onde vi a dica que vou apresentar neste artigo. Suponha que você tenha tabelado o preço unitário (coluna D) e a quantidade em estoque (coluna C) de vários produtos em duas filiais (Unidade 1 e Unidade 2) de uma rede de lojas, conforme ilustrado a seguir, com a fórmula exibida:

Para obter o valor total de produtos em estoque, basta usar a função SOMARPRODUTO dos intervalos C2:C9 (quantidades) e D2:D9 (respectivos preços unitários). Ou seja: 10*35,00 + 15*45,00 + … + 20*60,00 = 6.470,00, conforme resultado a seguir:

Mas e se quisermos somar condicionalmente, apenas por uma das filiais? O Excel não tem uma função “SOMARPRODUTOSE”, como tem a função SOMASE. Mas existe um truque! Se você tomar o intervalo A2:A9 onde estão os nomes da filiais, (A2:A9="Unidade 1") retorna uma lista (VERDADEIRO;FALSO;VERDADEIRO;…;FALSO). Se você utilizar esses valores lógicos em uma conta algébrica, o Excel converte automaticamente VERDADEIRO = 1 e FALSO = 0. Então, se aplicarmos a esse intervalo uma conta simples que retorne o próprio valor algébrico, como multiplicar por 1 (1*) ou inverter o sinal duas vezes (--), teremos 1*(A2:A9="Unidade 1") ou --(A2:A9="Unidade 1") resultando em (1;0;1;…0). Essas operações envolvem as diretrizes de fórmulas em matrizes no Excel que já abordei em outro artigo.

Pronto, utilize esse terceiro intervalo como parâmetro em SOMARPRODUTO e ele multiplicará por 0 onde não for Unidade 1, o que terá o efeito de desconsiderar esse produto (resultado 0) e multiplicará por 1 onde for Unidade 2, considerando o valor de cada produto onde for verdadeira a condição lógica. Ou seja: 10*35,00*1 + 15*45,00*0 + … + 20*60,00*0 = 3.925,00 Veja a fórmula aplicada na imagem do exemplo extraído do artigo citado:

Engenhoso, né? Espero que tenha sido útil e agradeço a André Victor pela dica original e pelas imagens reproduzidas aqui.