Se você já precisou contar ou somar valores com duas ou mais condições ao mesmo tempo e descobriu que o CONT.SES ou o SOMASES não resolvem tudo, você vai adorar conhecer a combinação de traço duplo com SOMARPRODUTO. Essa dupla é uma das mais poderosas do Excel para análise de dados avançada — ela permite criar fórmulas de contagem e soma com qualquer combinação de condições, inclusive condições que as funções nativas não conseguem processar diretamente. Neste artigo iremos mostrar como usar o traço duplo com SOMARPRODUTO no Excel com exemplos práticos do mundo real.
Por que SOMARPRODUTO e traço duplo trabalham tão bem juntos
O SOMARPRODUTO é uma função que multiplica os elementos de dois ou mais arrays e soma os resultados. Quando você usa uma condição lógica como critério dentro do SOMARPRODUTO — por exemplo (A1:A10=”Sul”) — o Excel gera um array de VERDADEIRO e FALSO para cada linha. O SOMARPRODUTO precisa multiplicar esses valores lógicos por outros arrays, e a multiplicação de VERDADEIRO e FALSO por números funciona na maioria das versões do Excel, mas pode gerar comportamentos inconsistentes em algumas situações ou fórmulas mais complexas.
O traço duplo resolve essa inconsistência convertendo explicitamente o array de VERDADEIRO/FALSO em 1/0 antes da multiplicação. Quando você escreve =SOMARPRODUTO(–(A1:A10=”Sul”); B1:B10), o –(A1:A10=”Sul”) gera um array de 1s e 0s, e a multiplicação por B1:B10 é sempre confiável. Linhas onde a condição é verdadeira (1) contribuem com o valor de B. Linhas onde é falsa (0) contribuem com zero. A soma total é exatamente a soma dos valores em B onde A=”Sul”. O traço duplo garante a robustez da fórmula em qualquer versão do Excel e com qualquer tipo de dado nos arrays.
Outro motivo pelo qual a combinação traço duplo + SOMARPRODUTO é tão poderosa é que o SOMARPRODUTO aceita qualquer tipo de expressão lógica como critério — não apenas as comparações simples que o CONT.SES e o SOMASES aceitam. Você pode usar funções como É.NÚM, É.TEXTO, DIA.DA.SEMANA, ESQUERDA, EXT.TEXTO e qualquer outra função do Excel que retorne VERDADEIRO/FALSO como parte de um critério dentro do SOMARPRODUTO. O traço duplo converte o resultado dessas funções em 1/0 e a multiplicação faz o resto.
Contagem com múltiplas condições usando traço duplo e SOMARPRODUTO
Para contar quantas linhas atendem a duas condições simultaneamente — por exemplo, vendedor “João” E região “Sul” —, o CONT.SES funciona: =CONT.SES(A:A;”João”;B:B;”Sul”). Mas quando as condições envolvem funções que o CONT.SES não aceita diretamente como critério, o SOMARPRODUTO com traço duplo é a solução. Por exemplo, para contar quantas células em A começam com a letra “A” (CONT.SES não aceita ESQUERDA como critério): =SOMARPRODUTO(–(ESQUERDA(A1:A100;1)=”A”)).
Para contar com duas condições usando o SOMARPRODUTO e traço duplo, multiplique os arrays de critérios: =SOMARPRODUTO(–(A1:A100=”João”)*–(B1:B100=”Sul”)). Cada — converte um critério em 1/0, e a multiplicação de dois arrays de 1/0 resulta em 1 apenas quando ambos os critérios são verdadeiros simultaneamente (1*1=1) e 0 quando qualquer um é falso (0*1=0 ou 1*0=0 ou 0*0=0). O SOMARPRODUTO soma todos os 1s, contando exatamente as linhas onde as duas condições são verdadeiras ao mesmo tempo.
Para a condição OU (conta quando pelo menos uma das condições é verdadeira), some os arrays em vez de multiplicar: =SOMARPRODUTO(–((–(A1:A100=”João”)+(–(B1:B100=”Sul”)))>0)). Aqui, a soma dos dois arrays de 1/0 resulta em 0 quando nenhum critério é verdadeiro, 1 quando um dos dois é verdadeiro e 2 quando ambos são verdadeiros. O critério >0 transforma qualquer valor não-zero em VERDADEIRO, e o — externo converte em 1. O resultado é 1 para qualquer linha onde pelo menos um critério é verdadeiro. Essa construção de condição OU não tem equivalente direto no CONT.SES.
Somas condicionais avançadas com traço duplo e SOMARPRODUTO
Para somas com critérios que o SOMASES não consegue processar, o SOMARPRODUTO com traço duplo é ainda mais valioso. Imagine que você quer somar os valores em C onde a célula correspondente em A é um número (não texto): =SOMARPRODUTO(–(É.NÚM(A1:A100)); C1:C100). O É.NÚM verifica cada célula em A e retorna VERDADEIRO para números reais. O traço duplo converte em 1/0. A multiplicação por C filtra apenas os valores onde A é número. O SOMARPRODUTO soma. Tudo isso em uma única fórmula que o SOMASES não conseguiria fazer porque não aceita É.NÚM como critério.
Para somar por mês dentro de uma coluna de datas: =SOMARPRODUTO(–(MÊS(A1:A100)=3); C1:C100). O MÊS(A1:A100) extrai o mês de cada data e compara com 3 (março). O traço duplo converte a comparação em 1/0. A multiplicação por C filtra os valores de março. O SOMARPRODUTO soma. O SOMASES com critérios de data aceita datas exatas ou intervalos de data, mas não aceita facilmente “todos os registros do mês de março independente do ano” — a combinação SOMARPRODUTO + MÊS() + traço duplo resolve isso com elegância.
Para somas com critérios baseados no comprimento do texto: =SOMARPRODUTO(–(NÚM.CARACT(A1:A100)>5); C1:C100). NÚM.CARACT conta os caracteres de cada texto em A. A comparação >5 verifica quais textos têm mais de 5 caracteres. O traço duplo converte em 1/0. A multiplicação por C filtra os valores correspondentes. O SOMARPRODUTO soma. Esse tipo de critério baseado em análise de texto é completamente impossível no SOMASES e trivial com SOMARPRODUTO + traço duplo.
Comparando SOMARPRODUTO com traço duplo versus CONT.SES e SOMASES
Para condições simples com valores fixos como critérios (igual a um texto, maior que um número, dentro de um intervalo de datas), o CONT.SES e o SOMASES são mais legíveis e igualmente eficientes — use-os. Eles são funções nativas otimizadas para esse tipo de critério e qualquer pessoa familiar com Excel os entende imediatamente.
O SOMARPRODUTO com traço duplo é a escolha superior quando: as condições envolvem funções que CONT.SES/SOMASES não aceitam como critério direto (MÊS, ANO, DIA.DA.SEMANA, ESQUERDA, É.NÚM, É.TEXTO, NÚM.CARACT), quando você precisa de lógica OU além da lógica E, quando os critérios precisam ser calculados a partir de expressões mais complexas, ou quando você está combinando três ou mais condições de formas não suportadas pelas funções nativas.
Em termos de performance, CONT.SES e SOMASES são mais rápidos que SOMARPRODUTO com grandes volumes de dados porque são funções nativas otimizadas. Para bases com dezenas de milhares de linhas e muitas fórmulas, prefira as funções nativas quando elas conseguem resolver o problema. Reserve o SOMARPRODUTO com traço duplo para as situações onde não há alternativa mais simples — e nessas situações, ele é insubstituível.
Se você curtiu esse artigo onde mostramos como usar o traço duplo com SOMARPRODUTO no Excel para contagens e somas avançadas, compartilhe com as suas redes sociais e não se esqueça de deixar um comentário aqui embaixo caso você tenha ficado com alguma dúvida.