SOMARPRODUTO

Introdução

SOMARPRODUTO multiplica cada valor de um vetor por seu elemento correspondente em outro vetor, e retorna o valor somado. Por exemplo, se temos uma configuração como a mostrada na tabela abaixo,

e inserimos numa célula a fórmula
=SOMARPRODUTO(B2:B4;D2:D4)
obtemos 200, já que (2*10) + (3*20) + (4*30) = 20 + 60 + 120 = 200.
Com essa estrutura, não passa de uma função matemática. Um uso além, mais criativo de SOMARPRODUTO foi desenvolvido como é mostrado nas seções a seguir.

Desenvolvendo o uso do SOMARPRODUTO

Dentro do Excel há duas funções muito úteis que aceitam contadores condicionais e somadores condicionais, chamados CONT.SE e SOMASE. São funções muito usadas, mas limitadas porque podem calcular apenas em um único vetor, e fazer apenas um único teste condicional. A partir do Excel 2007, foram introduzidas duas novas funções: CONT.SES e SOMASES, que permite fazer mais de um teste condicional. Isso quer dizer que o uso da função SOMARPRODUTO? Mais adiante veremos que não, porque SOMARPRODUTO oferece mais flexibilidade ainda que essas funções.
Este site possui uma página que discute CONT.SE e SOMASE (clique no link que deseja visitar). No entanto, antecipando o poder de SOMARPRODUTO, se você dominá-la, essas funções não farão nenhuma falta a você.
Em algumas ocasiões é necessário fazer múltiplos testes condicionais ou testes duplos. Por exemplo, múltiplos testes condicionais são úteis para testar intervalos (ex. buscar uma data entre duas datas) e testes duplos condicionais são úteis para buscar simultaneamente mais de um parâmetro numa tabela (ex. procurar cachorro em uma matriz e gato em outra). Isso pode ser alcançado usando formulas matriciais do tipo:
=SOMA(SE(testeA;SE(testeB;etc
De acordo com o exposto, a fórmula torna-se obviamente ilegível, e é uma fórmula matricial. Ao usar o SOMARPRODUTO, o mesmo problema pode ser resolvido com muito mais facilidade.
Para entender como SOMARPRODUTO pode ser usado, considere a tabela abaixo:

  Podemos facilmente contar o número de Ford com:
=CONT.SE(B3:B11;"Ford")
o que nos retorna 4.
Do mesmo modo, é simples calcular o valor de todos Ford vendidos, usando:
=SOMASE(B3:B11;"Ford";F3:F11)
Pergunta: como sabemos quantos Ford foram vendidos em Junho, ou qual é o valor total deles? Pode-se fazer:
=SOMA(SE(B3:B11="Ford";SE(D3:D11="Junho";1;0);0))
, que é uma fórmula matricial (clique aqui para saber mais sobre o assunto), então é necessário que você digite ela e pressionepressione CTRL+SHIFT+ENTER, não apenas ENTER. Da mesma forma, o valor total das vendas é obtido através de
=SOMA(SE(B3:B11="Ford";SE(D3:D11="Junho";F3:F11;0);0))
, que também é uma fórmula matricial.
Como esse artigo é sobre SOMARPRODUTO, você espera que seja possível usar função para esse caso, e sim, é possível. A solução para calcular o número de Ford vendidos em Junho pode ser expressa por:
=SOMARPRODUTO((B3:B11="Ford")*(D3:D11="Junho"))
e o valor total através de:ravés de:
=SOMARPRODUTO((B3:B11="Ford")*(D3:D11="Junho")*(F3:F11))
Em minha opinião, a leitura dessa fórmula mostra com mais clareza seu objetivo.
Outro modo de usá-la seria incluirmos o operador + (soma) para contar quantos carros foram vendidos, sejam da Ford ou se foram vendidos em Junho. Então:
=SOMARPRODUTO((B3:B11="Ford")+(D3:D11="Junho"))
Observe que nesse caso, se a linha analisada for do fabricante Ford e do mês Junho, serão contadas duas ocorrências. Caso queira remover essa duplicidade, use:
=SOMARPRODUTO((B3:B11="Ford")+(D3:D11="Junho")-(B3:B11="Ford")*(D3:D11="Junho"))

SOMARPRODUTO Explicado

Entender como SOMARPRODUTO funciona vai te ajudar a determinar onde usá-lo, como você pode construir sua fórmula e como você pode extendê-la.
A abaixo mostra um exemplo de banco de dados que vamos usar. Nesse exemplo, o problema é encontrar quantos Ford foram vendidos. B2:B1 contém o fabricante, D2:D13 contém a categoria e F2:F13 tem o número de unidades vendidas. A fórmula para se chegar ao resultado é
=SOMARPRODUTO((B2:B13="Ford")*(D2:D13="A")*(F2:F13))

A primeira parte da fórmula (B2:B13) verifica o vetor de fabricantes que contém o valor Ford. Isso retorna um vetor de VERDADEIRO/FALSO vetor de VERDADEIRO/FALSO, que no caso é
{VERDADEIRO,FALSO,VERDADEIRO,VERDADEIRO,VERDADEIRO,VERDADEIRO,VERDADEIRO,FALSO,VERDADEIRO,VERDADEIRO,VERDADEIRO,VERDADEIRO}
Da mesma forma, as categorias são verificadas para o valor A em (D2:D13="A"). Novamente, isso retorna um vetor de VERDADEIRO/FALSO, ou
{FALSO,FALSO,VERDADEIRO,VERDADEIRO,FALSO,VERDADEIRO,VERDADEIRO,VERDADEIRO,VERDADEIRO,VERDADEIRO,VERDADEIRO,VERDADEIRO}
E finalmente, os números não são verificados, mas atribuídos, em (F2:F13), que retorna um vetor com os números {3,4,2,1,4,3,2,8,6,8,7,6} .
Então agora temos três vetores: dois de valores três vetores: dois de valores VERDADEIRO/FALSO e um de números. Isso é mostrado a seguir:

E é aí que as coisas começam a ficar interessantes. essantes. SOMARPRODUTO geralmente funciona em vetores de números, mas aqui temos vetores de valores VERDADEIRO/FALSO bem como vetores de números. Quando usamos o operador * (sinal de multiplicação), podemos obter valores numéricos de outros vetores. * tem o efeito de converter esses dois vetores de valores VERDADEIRO/FALSOEIRO/FALSO em um vetor resultante de valores 0/1. Multiplicar VERDADEIRO por VERDADEIRO retorna 1 (tente escrever numa célula =VERDADEIRO*VERDADEIRO e você obterá 1 como resposta), e qualquer outra combinação retorna 0. Então, quando duas condições são satisfeitas, obtemos o 1, enquanto que se qualquer uma de ambas as condições não são satisfeitas, obtemos 0. Multiplicar o primeiro vetor por valores VERDADEIRO/FALSO pelo segundo vetor VERDADEIRO/FALSO retorna um vetor composto de valores 0/1, ou
{0,0,1,1,0,1,1,0,1,1,1,1}
Esse novo vetor de valores 0/1 é então multiplicado pelo vetor de número de peças vendidas para nos fornecer um outro vetor de número de peças vendidas que satisfaz as condições dos dois testes. SOMARPRODUTO então soma os elementos desse vetor para nos dar um resultado.
A Tabela abaixo mostra uma representação virtual dos valores VERDADEIRO/FALSO em seus equivalentes numéricos 0/1 e seus resultados individuais da multiplicação. Daí, você deve perceber que SOMARPRODUTO chega a um resultado, que em nosso caso é 35.

A próxima tabela mostra a mesma representação virtual com valores 0/1 sem a coluna de unidades vendidas, isto é, usando o SOMARPRODUTO para contar o número de linhas que satisfazem as duas condições, ou
=SOMARPRODUTO((B2:B13=A1)*(D2:D13="A"))

Se você está conseguindo acompanhar essa explicação até agora, você pode estar pensando que embora estejamos usando a função a função SOMARPRODUTO, o operador * simplificou os múltiplos vetores num único vetor, sendo então que SOMARPRODUTO simplesmente soma os membros do vetor composto, isto é, não há produto. Isso é perfeitamente correto e válido: SOMARPRODUTO pode trabalhar num vetor único (experimente colocar 2, 3, 4 nas células A1, A2, A3 e insira =SOMARPRODUTO(A1:A3) numa célula, e o resultado 9 é retornado corretamente). De fato, nós precisamos do * apenas para converter os vetores que estão sendo testados para uma condição particular, não precisamos deles para o vetor que não é sujeita ao teste condicional. Então poderíamos também usar
=SOMARPRODUTO((B2:B13="Ford")*(D2:D13="A");(F2:F13))
NOTA: Quando se usa a função SOMARPRODUTO, todos os vetores devem ser do mesmo tamanho, já que os elementos correspondentes de cada vetor serão multiplicados entre eles. Entretanto, eles não precisam estar na mesma linha. As expressões da abaixo são equivalentes:ivalentes:

=SOMARPRODUTO(B2:B4;C2:C4)
Para a primeira tabela, e tabela, e
=SOMARPRODUTO(B2:B4;C3:C5)
Para a segunda tabela:
2*3 + 3*4 + 4*5 = 38

Formato do SOMARPRODUTO

Nos exemplos apresentados até agora, o formato tem sido
=SOMARPRODUTO((vetor1=condição1)*(vetor2=condição2)*(vetor3))
Como mencionado acima, poderíamos também usar
=SOMARPRODUTO((vetor1=condição1)*(vetor2=condição2);(vetor3))
no qual o operador * é necessário apenas para traduzir o vetor condicional resultante da operação entre os dois testes VERDADEIRO/FALSO em valores 0/1.
No que tange ao uso do operador aritmético que traduz os valores VERDADEIRO/FALSO em 0/1, poderíamos usar muitas outras formas para se chegar ao mesmo resultado. Dessa forma, é também possível traduzir cada um dos testes condicionais individualmente multiplicando-os por 1,
=SOMARPRODUTO((vetor1=condição1)*1;(vetor2=condição2)*1;(vetor3))
ou
=SOMARPRODUTO(1*(vetor1=condição1);1*(vetor2=condição2);(vetor3))
ou elevando à potência de 1,
=SOMARPRODUTO((vetor1=condição1)^1;(vetor2=condição2)^1;(vetor3))
ou adicionando 0,
=SOMARPRODUTO((vetor1=condição1)+0;(vetor2=condição2)+0;(vetor3))
ou
=SOMARPRODUTO(0+(vetor1=condição1);0+(vetor2=condição2);(vetor3))
ou até mesmo usar a função N,
=SOMARPRODUTO(N(vetor1=condição1);N(vetor2=condição2);(vetor3))
Esses métodos diferem no operador *, uma vez que os testes são aplicados individualmente em cada vetor.
Todos esses métodos funcionam apenas quando há mais de um vetor, então é realmente uma questão de preferência da forma de escrever a função. Se há apenas um único vetor, então o operador * não pode ser usado (já que não existem dois vetores a serem multiplicados), e então um dos métodos acima deve ser usado.
E ainda há mais um modo de se escrever a fórmula, que é usando o operador duplo de subtração, --, dessa forma:
=SOMARPRODUTO(--(vetor1=condição1);--(vetor2=condição2);(vetor3))
O sinal duplo de subtração também traduz os testes condicionais individuais.
Tem havido muita discussão de qual maneira é mais rápida que outra, ou qual é mais "padronizada" que outra, mas computacionalmente falando haverá alguns casos onde um método possui uma vantagem substancial sobre outro, e falando de padronização, devemos lembrar que tudo isso é um campo novo de estudo que ainda está em crescimento, e a função será usada principalmente por pessoas que não conhecerão uma padronização ou sequer se importam com isso.
Provavelmente é uma questão de gosto. É comum usar a notação do sinal duplo de subtração --, porque evita uma chamada de função (no caso de usar a função N), funciona em todas as situações (lembrar que o operador * não funciona com um único vetor), e simplesmente são ilegíveis as variações 1*, *1, ^1, +0 ou 0+.
Há ainda outra variação que vem sido usada recentemente, que é com o uso de um único sinal de subtração, da forma
=SOMARPRODUTO(-(vetor1=condição1);-(vetor2=condição2);(vetor3))
, mas não recomenda-se essa forma de construir a fórmula, já que o usuário deve atentar ao fato de que o número de sinais de subtração deve ser par, senão a fórmula retornará um resultado negativo. É querer complicação à toa, não?
Testes, como A=10 normalmente fornecem VERDADEIRO ou FALSO, e um operador é necessário somente se você quer relacionar um vetor de VERDADEIRO/FALSO para inteiros 0/1, como em
=SOMARPRODUTO(--(B5:B1953=101))
Vetores de SOMARPRODUTO são normalmente separados por ponto e vírgula (no Excel em Português). Então, para manter a formatação, se você tem múltiplas condições, você pode usar o -- em ambas as condições como em
=SOMARPRODUTO(--(B5:B1953=101);--(C5:C1953=7))
Mas, se você simplesmente multiplicar dois vetores VERDADEIRO/FALSO, fica implícito que a saída de valores é de 0/1 e então não é necessário o ponto e vírgula, podendo ser usado
=SOMARPRODUTO((B5:B1953=101)*(C5:C1953=7))
Enfim, vetores de valores podem usar o mesmo operador, ou pode ser atribuído sem nenhum operador, apenas sendo separado com o ponto e vírgula. Logo, a mesma fórmula acima pode ser escrita como:
=SOMARPRODUTO(--(B5:B1953=101);--(C5:C1953=7);(D5:D1953))
ou
=SOMARPRODUTO(--(B5:B1953=101)*(C5:C1953=7);(D5:D1953))
ou
=SOMARPRODUTO(--(B5:B1953=101);--(C5:C1953=7);--(D5:D1953))
ou
=SOMARPRODUTO(--(B5:B1953=101)*(C5:C1953=7)*(D5:D1953))
ou
=SOMARPRODUTO(--(B5:B1953=101);--(C5:C1953=7)*(D5:D1953))
Se o resultado é o produto de duas condições sendo multiplicado, não vejo problemas em multiplicá-los juntos já que dessa forma os valores VERDADEIRO/FALSO serão traduzidos em 0/1 permitindo então a soma:
=SOMARPRODUTO((condição1)*(condição2))
No entanto, se há apenas uma condição, você pode forçar o 0/1 com o duplo sinal de subtração --
=SOMARPRODUTO(--(condição1))
Você poderia chegar a esse resultado da mesma forma com
=SOMARPRODUTO(1*(condição1))
E da mesma forma chegar ao resultado anterior através de
=SOMARPRODUTO(--(condição1);--(condição2))
Não há nenhuma situação que eu saiba em que uma solução usando o -- não pode ser alcançada se for usado o *, a não ser que a função TRANSPOR seja usada na função SOMARPRODUTO.
Então, como você pode ver, há um grande número de construções diferentes para fazer a construção da fórmula SOMARPRODUTO, e a escolha fica a seu critério. Na opinião de Harlan Grove, que escreveu uma vez este parágrafo justificando porque ele prefere o uso do duplo operador de subtração para converter valores VERDADEIRO/FALSO em 0/1:
"(...) não é por causa da velocidade de se escrever o operador duplo de subtração que eu o uso, e sim porque devido a como ele é digitado, é mais difícil se enganar com erros de digitação usando o duplo sinal de subtração do que quando se usa as alternativas ^1, 1*, 0+. Além disso, como eu leio da esquerda para direita, eu prefiro que o operador de tradução de condições fique à esquerda da expressão do que à direita. Além disso, o sinal -- simplesmente parece ser melhor que o 1* ou o 0+. Usar também a função N é outra alternativa, possivelmente mais clara, mas envolve uma nível a mais de função a ser chamada, então por isso não a uso."

Vantagens do SOMARPRODUTO em Relação à Funções Similares

SOMARPRODUTO possui outras duas vantagens consideráveis. A primeira é que funciona com workbooks abertos, e a segunda é que a manipulação de valores de texto pode ser costurado de acordo com a necessidade.
No caso de outra Pasta de Trabalho, a função SOMASE pode ser usada para calcular um valor, como em
=SOMASE('[1o Semestre.xls]TAXAS'!$K$11:$K$13;">1")
Essa é uma boa vantagem, e o valor se mantém se a outra Pasta de Trabalho é fechada, mas assim que a planilha é recalculada, a fórmula retorna #VALOR. Da mesma forma, se a fórmula é entrada com o outro workbook fechado, o resultado #VALOR é imediatamente retornado.
SOMARPRODUTO, no entanto, conserta esse problema. A fórmula
=SOMARPRODUTO(--('[TaxasAtuais.xls]TAXAS'!$K$11:$K$13>1);--('[TaxasAtuais.xls]TAXAS'!$K$11:$K$13))
retorna o mesmo valor, mas ainda irá funcionar quando a outra Pasta de Trabalho estiver fechado e a planilha for recalculada, e pode inicialmente ser entrada referenciando a Pasta de Trabalho fechada, sem retornar o erro #VALOR. Essa já é a primeira vantagem de SOMARPRODUTO em relação às funções CONT.SE, CONT.SES, SOMASE e SOMASES.
2a. Vantagem:
A segunda maior vantagem é ser capaz de lidar com texto em colunas numéricas diferentemente. Considere o conjunto de dados mostrado na Tabela abaixo:

Usando o SOMARPRODUTO, podemos retornar tanto um erro, ou ignorar o texto. Isso pode ser útil quando queremos ignorar erros, ou simplesmente queremos rastrear erros (e presumivelmente corrigi-los posteriormente).
Se usarmos a versão
=SOMARPRODUTO((B2:B5="x")*(C2:C5))
um erro #VALOR!
Para ignorar erros, use essa versão corrigida que usa sinal de negativo duplo:
=SOMARPRODUTO(--(B2:B5="x");(C2:C5))
Essa é outra vantagem em relação às funções CONT.SE, CONT.SES, SOMASE e SOMASES.
3a. Vantagem:
Além disso, o SOMARPRODUTO pode ser um operador OU, como no exemplo abaixo:
=SUMPRODUCT(--(((Fabricante="BMW")+(Mês="Maio"))>=1);(Preço))
Observe que se uma das condições for verdadeira (Fabricante="BMW" ou Mês="Maio"), o registro será computado em SOMARPRODUTO.
4a. Vantagem:
Soma baseada em dois critérios tal que um critério está numa coluna e outro está numa linha. Exemplo abaixo:

A fórmula é:
=SOMARPRODUTO((G17:G19="B")*(H16:K16="E");(H17:K19))
O resultado retorna, corretamente, 10 (porque faz-se a conta 2 + 8).
5a. Vantagem:
Você pode contar o número de letras numa célula com o uso de SOMARPRODUTO. O exemplo abaixo mostra conta quantas letras e há na célula A1, que contém Felipe Benza:

A fórmula é:
=SOMARPRODUTO(--(EXT.TEXTO(A1;LIN(INDIRETO("1:" & NÚM.CARACT(A1)));1)="e"))
, que retorna corretamente 3.

Exemplos

Para ilustrar mais ainda o poder da função da função SOMARPRODUTO, alguns exemplos que variam de nível médio a avançado serão apresentados.

Combinando Valores Entre Dois Intervalos

Contar o número de maçãs vendidas num dado período representado pela célula A1. Sabe-se que C5:C309 possui datas de venda do produto correspondente em H5:H309.
Solução:
É um teste simples. Se a data a ser testada está numa célula poderíamos escrever:
=SOMARPRODUTO((C5:C309>$A$1)*(H5:H309="Maçã"))
Para usar datas em qualquer idioma de Excel (por exemplo, os americanos usam as datas no formato mm/dd/aaaa), pode-se usar o sinal de subtração duplo para converter a data:
=SOMARPRODUTO((C5:C309>--"01/01/2009")*(H5:H309="A"))

Combinando Valores Entre Dois Intervalos e um NOME

Contar o número de células em A42:A407 onde a data é anterior à de hoje, e simultaneamente a célula correspondente no intervalo J42:J407 é igual a alguma célula de outro vetor representado por um NOME definido.

O teste da data é facilmente obtido por
($A$42:$A$407<HOJE())
Suponha que exista um certo NOME chamado NomeQualquer, que é representado pelo intervalo F47:F49 (observe que o tamanho do vetor do NOME não precisa ser igual ao vetor onde se busca valores dentro do NOME).
O que queremos é achar todos os valores cuja linha simultaneamente tenha uma data em A menor que hoje e que o valor correspondente de J dentro de algum lugar de NomeQualquer. Para resolver a segunda parte do teste dentro da fórmula de SOMARPRODUTO, as funções CORRESP e ÉNÚM são usadas, e obtemos como resultado final:
=SOMARPRODUTO(--(ÉNÚM(CORRESP($J$42:$J$407;NomeQualquer;0)));--($A$42:$A$407<HOJE()))
Para o exemplo do banco acima, o resultado final será 1, visto que apenas a proposição da linha 42 é satisfeita.
Vamos fazer uma análise mais detalhada da expressão (ÉNÚM(CORRESP($J$42:$J$407;NomeQualquer;0)) que integra a primeira parte da fórmula. Caso exista algum valor de $J$42:$J$407 em NomeQualquer, a função CORRESP retorna o número relativo da posição desse valor dentro de NomeQualquer.
Quando ÉNÚM associamos seu uso à função CORRESP, podemos interpretar então que ÉNÚM só será VERDADEIRO se uma célula do vetor analisado existir dentro de NomeQualquer. Logo, temos então a conversão VERDADEIRO/FALSO em valores 0/1 da primeira e segunda partes da expressão de SOMARPRODUTO porque o operador * é usado, fazendo com que a fórmula funcione corretamente.
Alternativamente, poderíamos escrever, para obter a mesma solução, a expressão
=SOMARPRODUTO(--(ÉNÚM(CORRESP($J$42:$J$407;NomeQualquer;0)));--($A$42:$A$407<HOJE()))