Como usar a função SUMPRODUCT no Planilhas Google

O SUMPRODUTO no Planilhas Google multiplica itens correspondentes em matrizes de tamanho igual e retorna a soma dos resultados. Isso é útil em muitas situações em que você precisa multiplicar itens em matrizes e somá-los.

Dê uma olhada no exemplo abaixo. Aqui eu tenho uma lista de itens, seus preços, quantidades e preços totais de venda.

Calculei os preços de venda correspondentes em coluna D, ou seja, as células D2 a D6.

Para fazer isso, usei funções de multiplicação para multiplicar cada preço por sua quantidade.

Em seguida, usei a função SUM para calcular o preço de venda total de todos os itens (célula D7).

Esse é um processo bastante demorado. Felizmente, há uma alternativa muito mais simples: a função SUMPRODUCT.

Sintaxe

• array1 - o array ou referência de endereço para o intervalo de células cujos valores a função SUMPRODUCT multiplicará pelos valores correspondentes no segundo array ou intervalo de células.
• array2, ... - arrays opcionais e adicionais ou referências de endereço ao intervalo de células com o mesmo tamanho de array1. A função SUMPRODUCT multiplicará esses valores pelos valores correspondentes na primeira matriz ou intervalo de células.

Como usar a função SUMPRODUCT

No exemplo acima, usei a função SUM para obter os resultados desejados.

Veja como fazer o mesmo cálculo usando a função SUMPRODUCT, que é mais eficiente.

No instantâneo acima, digitei a função SUMPRODUCT na célula D8, digitando =SUMPRODUCT(B2:B6,C2:C6).

Compare o valor retornado com o da célula D7: é exatamente o mesmo.

O intervalo B2:B6 é a entrada para a matriz1, enquanto C2:C6 é a entrada para a matriz2. O SUMPRODUCT multiplica e soma os valores em uma única etapa - muito mais fácil!

Embora isso já seja muito mais prático do que a abordagem anterior, fica ainda melhor quando o conjunto de dados de origem é alterado, especialmente quando você insere novas linhas.

Agora tentarei inserir um novo registro entre a 4ª e a 5ª linhas:

Você notará que a função SUMPRODUCT agora está realocada na célula D9. Ela ajustou automaticamente o cálculo para a última modificação, enquanto a função SUM não foi alterada.

Se você quisesse fazer a função SUM funcionar, precisaria atualizar a função em D5 e repeti-la toda vez que adicionasse uma nova linha, o que poderia rapidamente se tornar um trabalho demorado.

SUMPRODUCT com várias matrizes

Até agora, usei a função SUMPRODUCT para multiplicar e somar duas matrizes. Mas você pode usar a função SUMPRODUCT com quantas matrizes quiser!

No exemplo abaixo, adicionei uma terceira coluna que mostra a comissão % paga por cada produto vendido.

Usando a função SUMPRODUCT, posso multiplicar rapidamente as três colunas para cada item e, em seguida, somar o total. Fiz isso digitando =sumproduct(B2:B6,C2:C6,D2:D6).

Uso de SUMPRODUCT com condições

É interessante notar que você também pode usar a função SUMPRODUCT para somar itens que atendam a determinados critérios. Isso é semelhante à função Função COUNTIFS.

Demonstrarei isso em um exemplo abaixo. A função conta (ou melhor, soma) as instâncias em que todas as condições da matriz são avaliadas como VERDADEIRAS.

Aqui, estou solicitando o número de itens que atendem aos três critérios a seguir:

1. São vegetais.
2. Têm 25 ou mais calorias.
3. Contêm mais de 5g de carboidratos.

Como você pode ver, a função SUMPRODUCT pesquisa o conjunto de dados e me diz que há 2 itens que atendem a esses critérios.

A lógica aqui pode ser confusa no início, mas lembre-se de que, no Planilhas Google, Verdadeiro = 1 e Falso = 0. Portanto, quando você usa a função SUMPRODUCT com condições que são Verdadeiras ou Falsas, você está apenas multiplicando 1 e 0. Obviamente, qualquer valor Falso (em outras palavras, um 0) fará com que toda a entrada seja 0. É por isso que todos os critérios devem ser verdadeiros para que o item seja contado.

Para ilustrar melhor isso, incluí a tabela abaixo.

Observe que o produto de TRUE, TRUE e TRUE será 1, enquanto o produto de qualquer combinação que envolva um FALSE será avaliado como 0.

Depois de entender como o SUMPRODUCT trata os valores verdadeiros e falsos, você poderá experimentar usos mais complexos da função.

Você pode usar outras funções para gerar verdadeiro ou falso para cada item com base em qualquer critério que desejar!

No exemplo abaixo, mostrarei uma maneira de fazer isso.

Fórmulas complexas de SUMPRODUCT

Vou ampliar o exemplo da comissão de vendas. Digamos que, agora, somente os itens com um número de item contendo um 2 receberão comissão. A comissão para os outros itens foi dispensada.

Para solicitar que a função calcule isso, adicionei uma nova coluna na qual uso o parâmetro ISNUMBER e PESQUISA para determinar se há um 2 no número do item (faço isso digitando a fórmula =isnumber(search(2,A2))).

Agora posso fazer um SUMPRODUCT com 4 matrizes, e os valores falsos (ou seja, 0s) eliminarão a comissão dos produtos sem um 2 no número do item, deixando apenas os produtos relevantes incluídos no cálculo.

Como você pode ver, é possível usar a função SUMPRODUCT para realizar tarefas simples e complexas com muita eficiência!

Um uso comum é calcular uma média ponderada, em que SUMPRODUCT multiplica cada valor pelo seu peso e soma os resultados.

Procurando mais dicas sobre o Planilhas Google?

Confira abaixo nossas outras postagens sobre fórmulas!