Como usar a função SUMPRODUCT do Excel

Você pode pensar que o Função SUMPRODUCT do Excel é bastante enfadonha à primeira vista, mas a função é, na verdade, extremamente útil e versátil. Embora seu objetivo principal seja multiplicar e somar dados em matrizes, você também pode usá-la para uma ampla gama de aplicações mais complicadas. Dito isso, vou me aprofundar nos detalhes dessa função.

Sintaxe

• matriz1 é a primeira matriz que você deseja multiplicar com as outras matrizes e, em seguida, adicionar os resultados
• matriz2 e todas as outras matrizes em diante são entradas opcionais. A função multiplicará os componentes de todas as matrizes e, em seguida, somará os resultados

Como usar SUMPRODUCT no Excel

Começarei mostrando um exemplo básico da função SUMPRODUCT do Excel. Como mencionei anteriormente, a função é usada principalmente para multiplicar matrizes e somar os resultados.

Dê uma olhada no exemplo abaixo. Tenho alguns dados de vendas de uma barraca de frutas.

Fiz isso na coluna F, mas você pode ver que esse método requer uma nova coluna e vários cálculos individuais. A função SUMPRODUCT é uma maneira muito mais rápida de resolver isso!

Por digitação =SUMPRODUCT(B2:B7,C2:C7) Instruo a função a multiplicar cada quantidade pelo preço correspondente e somar o total. Isso me dá a receita em uma única célula!

Adicionei os cálculos mais longos na coluna F para que você possa ver que o resultado é o mesmo - o SUMPRODUCT é apenas muito mais rápido.

Usando SUMPRODUCT para contar

Quando você insere várias matrizes, o SUMPRODUCT as multiplica e soma. Mas e se você inserir apenas uma matriz? Como você viu na sintaxe, apenas o primeiro array é necessário para que a função funcione.

Quando você insere apenas uma matriz, a função simplesmente soma essa matriz. Você pode ver isso na captura de tela abaixo, onde eu uso a função =SUMPRODUCT(B2:B7) para somar a quantidade total de itens vendidos.

Nesse caso, a função SUMPRODUCT funciona exatamente como a função SUM!

Combinação com outras funções

Uma das vantagens da função SUMPRODUCT é que você pode combiná-la com outras funções.

No exemplo abaixo, eu a combino com a função LEN para encontrar o número total de caracteres com a fórmula =SUMPRODUCT(LEN(A2:A7)).

Outros operadores

Com base em seu nome, você pode pensar que a função SUMPRODUCT só pode fazer a multiplicação entre matrizes. Entretanto, a função também pode realizar operações aritméticas (*, /, +, -) entre matrizes. O tema constante é que ela sempre executa a operação entre as matrizes e, em seguida, soma o total.

Dê uma olhada no exemplo abaixo. Uso a função SUMPRODUCT para somar as colunas Preço e Imposto e, em seguida, multiplico pela quantidade. Faço isso com a fórmula =SUMPRODUCT(B2:B7*(C2:C7+D2:D7))./p>

Dica: para especificar qual operação você deseja executar, basta adicionar o sinal entre as matrizes em vez de usar uma vírgula. Também é uma boa ideia usar colchetes para garantir que as operações sejam realizadas na ordem desejada.

Contagem com texto

Já usei o SUMPRODUCT com matrizes de números, mas agora vou demonstrar como você pode usá-lo com texto.

Neste exemplo, uso a coluna Item como minha matriz, mas também especifico que quero apenas células com a string de texto “Banana” (adicionei outra linha de bananas apenas para este exemplo).

Você notará algo estranho na fórmula usada aqui, =SUMPRODUCT(-(A2:A8="Banana")). Ele tem dois sinais negativos (-) antes dos colchetes. Não se preocupe muito com isso - é apenas o que a fórmula precisa para computar os resultados que eu quero.

Quando o SUMPRODUCT avalia a expressão A2:A8=”Banana”, ele percorre a coluna e atribui um VERDADEIRO ou FALSO a cada célula. Para somar esses valores, preciso convertê-los de VERDADEIRO/FALSO em um número.

O “-“ é como multiplicar por -1 duas vezes: uma vez para converter TRUE/FALSE em um número e outra vez para cancelar o negativo. Assim, VERDADEIRO se torna 1 e FALSO se torna 0. A função então soma esses valores e indica corretamente que há 2 entradas Banana.

Observe que você poderia obter o mesmo efeito usando 1* em vez de - (ou seja, multiplicar por 1 em vez de -1 duas vezes), mas é um pouco mais rápido digitar os dois traços.

SUMPRODUCT com string de texto e várias matrizes

Agora vou combinar alguns dos exemplos anteriores. Aqui, quero a receita total da venda de bananas. Preciso usar 3 matrizes diferentes aqui.

A primeira analisa as sequências de texto na coluna Item e diz à função para considerar apenas as bananas (atribuindo a elas um 1 e ao restante um 0). Em seguida, ela multiplica isso pelo preço e pela quantidade e soma o resultado para obter a receita total das bananas.

A fórmula aqui é =SUMPRODUCT((A2:A8="Banana")*B2:B8*C2:C8). Observe que, diferentemente do último exemplo, não preciso de um - antes do primeiro array. Isso ocorre porque o TRUE/FALSE gerado por A2:A8=”Banana” é convertido em um número quando multiplicado pelos outros vetores. Portanto, só precisei desse truque quando era para apenas um array!

Esse exemplo realmente demonstra o poder da função SUMPRODUCT. Ela executa as operações que eu quero em cada entrada dos meus dados e também filtra seletivamente para fornecer apenas os resultados dos itens que eu quero incluir. Tudo em uma única fórmula!

Espero que agora você veja a utilidade da função SUMPRODUCT e como usá-la para realizar cálculos complexos com uma fórmula simples!