SOMASES e CONT.SES: somar e contar com várias condições no Excel

Saber quanto um vendedor vendeu no total é fácil. O difícil é quando a pergunta tem várias partes: quanto a Ana vendeu na região Norte, quantos pedidos acima de 200 houve em março ou quantas vezes o mesmo produto aparece para o mesmo vendedor. É para isso que existem SOMASES e CONT.SES.

As duas funções aplicam todas as condições que você indicar ao mesmo tempo e só consideram as linhas que atendem a todas elas. Você vai ver a sintaxe, exemplos com texto, números, datas e curingas, como montar uma tabela de resumo e como usá-las para localizar registros repetidos.

Os dados de exemplo

Vamos usar este registro de vendas, com os cabeçalhos na linha 1 e os dados nas linhas 2 a 9. A coluna A contém datas reais, não texto:

Sintaxe das duas funções

As duas estão disponíveis desde o Excel 2007 e também existem no Google Planilhas e no LibreOffice Calc.

=SOMASES(intervalo_soma;intervalo_critérios1;critérios1;intervalo_critérios2;critérios2)

=CONT.SES(intervalo_critérios1;critérios1;intervalo_critérios2;critérios2)

• intervalo_soma: as células com os números que serão somados (apenas em SOMASES).
• intervalo_critérios: a coluna onde cada condição é verificada.
• critérios: a condição, por exemplo "Ana", ">=200" ou uma referência a uma célula.

Você pode adicionar até 127 pares de intervalo e critério. Atenção a um detalhe que confunde muita gente: no SOMASE o intervalo a somar vai no final, enquanto no SOMASES ele vem primeiro.

Somar e contar com várias condições

Para somar as vendas da Ana na região Norte:

=SOMASES(E2:E9;B2:B9;"Ana";C2:C9;"Norte")

O resultado é 520: só contam as linhas 2 e 4. A venda da Ana na região Sul fica de fora. Para contar quantas vendas do Produto A houve na região Norte:

=CONT.SES(C2:C9;"Norte";D2:D9;"Produto A")

Retorna 3 (linhas 2, 5 e 9).

Critérios com operadores

Para comparar números, escreva o operador e o valor juntos entre aspas. Esta fórmula conta as vendas da região Norte de 200 ou mais:

=CONT.SES(E2:E9;">=200";C2:C9;"Norte")

O resultado é 4. Os operadores válidos são =, <>, >, <, >= e <=. Por exemplo, "<>Sul" conta tudo o que não for Sul.

Critérios com datas

Para somar um período, coloque a data inicial em K1 e a final em K2 e una o operador à célula com &:

=SOMASES(E2:E9;A2:A9;">="&K1;A2:A9;"<="&K2)

Com 1º e 31 de março nessas células, o resultado é 1670. Repare que o mesmo intervalo aparece duas vezes, uma para cada limite. Usar células em vez de digitar a data dentro da fórmula evita problemas com o formato de data de cada país.

Curingas

Nos critérios de texto, você pode usar * (qualquer quantidade de caracteres) e ? (um único caractere). Para contar as vendas dos vendedores cujo nome começa com M:

=CONT.SES(B2:B9;"M*")

Retorna 2. Se precisar procurar um asterisco de verdade, digite ~*.

Criar uma tabela de resumo

A aplicação mais útil é montar um pequeno relatório cruzado. Digite os vendedores em G2:G4, as regiões em H1 (Norte) e I1 (Sul) e, em H2, esta fórmula:

=SOMASES($E$2:$E$9;$B$2:$B$9;$G2;$C$2:$C$9;H$1)

Depois copie-a para o resto da tabela. Os cifrões $ são o segredo:

1. Os intervalos de dados têm $ na linha e na coluna, para não se deslocarem ao copiar.
2. $G2 fixa a coluna: cada linha da tabela sempre lê o seu vendedor.
3. H$1 fixa a linha: cada coluna sempre lê a sua região.
4. Pressione F4 várias vezes sobre uma referência para alternar entre os tipos de fixação.

A soma da tabela inteira (2110) coincide com o total da coluna E, uma verificação rápida de que nenhuma linha ficou de fora.

Quando você precisa de uma condição OU

Todas as condições dessas funções são combinadas com E: a linha precisa atender a todas. Se você quiser somar as vendas da Ana ou do Luis, o jeito mais claro é somar duas fórmulas:

=SOMASES(E2:E9;B2:B9;"Ana")+SOMASES(E2:E9;B2:B9;"Luis")

O resultado é 1520 (1020 da Ana mais 500 do Luis). Só tome cuidado para que as condições não se sobreponham; se uma linha pudesse atender às duas, seria somada duas vezes.

Detectar registros duplicados com CONT.SES

CONT.SES também serve para saber quantas vezes uma combinação de valores aparece. Em uma coluna auxiliar, digite na linha 2:

=SE(CONT.SES($B$2:$B$9;B2;$D$2:$D$9;D2)>1;"Repetido";"")

A fórmula conta quantas linhas têm o mesmo vendedor e o mesmo produto que a linha atual. Nos dados de exemplo, ela marca como repetidos os pares Ana e Produto A (linhas 2 e 7) e Luis e Produto A (linhas 6 e 9). É útil para revisar antes de apagar: você pode filtrar a coluna auxiliar e decidir o que fazer com cada caso.

Se você quer simplesmente eliminar as linhas repetidas de um arquivo grande, nossa ferramenta gratuita para remover duplicados faz isso com arquivos XLSX, XLS, CSV e ODS, sem fórmulas.

Erros comuns

• Intervalos de tamanhos diferentes. Se o intervalo de soma for E2:E9 e um intervalo de critérios for B2:B8, o Excel retorna #VALOR!. Todos devem ter o mesmo número de linhas.
• Operador sem concatenar. ">=K1" compara com o texto K1, não com o valor da célula. O correto é ">="&K1.
• Números armazenados como texto no intervalo de soma. Eles são ignorados, e o total fica menor do que o esperado. Acontece muito com dados colados de outros programas ou importados de CSV.
• Pastas de trabalho externas fechadas. Se os intervalos estiverem em outro arquivo e ele estiver fechado, a fórmula retorna #VALOR!. Abra o arquivo ou copie os dados para a mesma pasta de trabalho.
• Espaços sobrando. “Ana ” com um espaço no final não corresponde a “Ana”. Limpe a coluna com ARRUMAR antes de calcular.

Conclusão

SOMASES e CONT.SES respondem a perguntas com várias condições sem tabelas dinâmicas nem filtros manuais. Com critérios em células, referências mistas e o operador &, você monta relatórios que se atualizam sozinhos quando novos dados são adicionados.

Mantenha os intervalos do mesmo tamanho, confira os totais com uma soma geral e limpe os dados antes de começar: a maioria dos resultados estranhos vem de espaços, textos ou linhas repetidas.

Perguntas frequentes