Trilha de aprendizado · Nível 4 · Tutorial 2

Totais e contagens por critério com SOMASE e CONT.SE

Calcular totais por categoria e contagens por condição no Excel ou no Calc, usando SOMASE e CONT.SE. O aluno montará um resumo de despesas com critérios editáveis e conferirá os resultados diretamente nos poucos registros do exemplo, sem depender de filtros ou tabelas dinâmicas.

  • Nível: Intermediário
  • Duração: 18 min
  • 8 passos
Totais e contagens por critério com SOMASE e CONT.SE

Passo 1 de 8

Escolher entre contar e somar

Diferencie contagem e total e monte a base de despesas que servirá de referência para as fórmulas.

Entenda a pergunta antes de escolher a função

Uma condição, dois tipos de resultado

Uma agregação por um único critério reúne o resultado de vários registros que atendem à mesma condição. Neste tutorial, a condição pode ser uma categoria, como Escritório.

Há duas perguntas parecidas, mas com respostas diferentes:

  • “Quantos lançamentos são de Escritório?” pede uma contagem. Use CONT.SE.
  • “Qual é o valor total dos lançamentos de Escritório?” pede a soma dos valores. Use SOMASE.

As duas funções estão disponíveis no Excel e no Calc. Usaremos nomes de funções em pt-BR, fórmulas iniciadas com = e argumentos separados por ponto e vírgula. Nos próximos passos, você montará as fórmulas; por enquanto, foque na pergunta que cada função responde.

Contagem e total usam os mesmos registros

Diagrama de planilha em que dois lançamentos da categoria Escritório atendem ao critério; eles geram uma contagem de 2 e um total de R$ 220,00.

Os mesmos registros podem responder a duas perguntas: quantos são e qual é o total de seus valores.

Prepare a planilha para a prática

Monte a base de despesas

No seu computador, crie uma planilha no Excel ou no Calc e renomeie a aba como Despesas. Digite a base abaixo em A1:C9. Os valores da coluna C devem ser números; depois, se quiser, aplique o formato de moeda brasileira.

Cada linha é um lançamento. Não inclua os cabeçalhos nos intervalos que serão usados mais adiante.

Base do Escritório Horizonte

Digite estes dados em A1:C9 na aba Despesas.

Registros que serão usados durante todo o tutorial.
LançamentoCategoriaValor
PapelariaEscritório120
Transporte por aplicativoTransporte40
CartuchosEscritório100
AlmoçoAlimentação60
ÔnibusTransporte20
LancheAlimentação40
Suporte remotoServiços80
Ajuste sem cobrançaAjustes0

Prepare a área de resumo

Na mesma aba, prepare a área de resumo, sem inserir fórmulas ainda. Ela guardará a categoria e o limite que serão usados depois, além dos quatro indicadores.

Deixe F6:F9 vazias por enquanto. Você preencherá esses resultados passo a passo.

Entradas e indicadores do resumo.
CélulaConteúdo
E2Categoria
F2Escritório
E3Limite
F3100
E6Contagem por categoria
E7Total por categoria
E8Contagem acima do limite
E9Total acima do limite

Pratique a escolha entre contar e somar

Escolha a função pela pergunta

Associe cada pergunta à função mais adequada.

Toque em um item e depois no par correspondente.

Passo 2 de 8

Contar ocorrências com CONT.SE

A estrutura da contagem

CONT.SE conta células que atendem ao critério

Como a pergunta é “quantos lançamentos pertencem à categoria Escritório?”, use CONT.SE. Ela verifica um intervalo de critérios e devolve uma contagem das células que atendem ao critério.

A estrutura é:

=CONT.SE(intervalo de critérios;critério)

Para um critério textual literal, escreva o texto entre aspas duplas. Na aba Despesas, as categorias estão em B2:B9. Portanto, a fórmula é:

=CONT.SE(B2:B9;"Escritório")

Os valores da coluna C não entram nessa contagem: cada célula da coluna B que contém exatamente Escritório vale uma ocorrência.

Quais células são contadas

Diagrama da planilha em que B2 e B4, ambas com a categoria Escritório, estão contornadas e identificadas como Atende. O resultado da contagem é 2; os valores 120 e 100 na coluna C não são usados para contar.

B2 e B4 atendem ao critério textual. Cada uma acrescenta 1 à contagem, independentemente do valor associado na coluna C.

Monte a fórmula

Pratique na célula F6

Na área de resumo, o indicador Contagem por categoria fica em E6 e o resultado deve ficar em F6.

Complete a fórmula para contar as células da coluna de categorias que correspondem ao texto solicitado. Depois de confirmar, observe o resultado exibido em F6.

Complete a CONT.SE

Complete a fórmula que deve ser inserida em F6.

excel
=(;)

Aplique e justifique o resultado

Conferência da contagem

Faça a conferência final diretamente na sua planilha. Em F6, insira a fórmula de contagem para a categoria Escritório e use a coluna B como evidência.

Na resposta, informe os três elementos pedidos: a fórmula digitada, o resultado mostrado em F6 e os dois endereços de célula que sustentam a contagem. Não informe valores monetários da coluna C.

Escreva pelo menos 15 caracteres (0/15).

Passo 3 de 8

Somar valores das linhas correspondentes

Relacionar a categoria aos valores

Da categoria ao total

Para obter o total de uma categoria, use SOMASE. Ela verifica quais células do intervalo de critérios atendem ao critério e soma os valores que estão nas mesmas linhas do intervalo de soma.

Estrutura:
=SOMASE(intervalo de critérios;critério;intervalo de soma)

No controle de despesas, as categorias estão em B2:B9 e os valores correspondentes estão em C2:C9. Para a categoria Escritório, entram os valores de Papelaria e Cartuchos.

Linhas que entram no total

Diagrama da planilha em que B2 Escritório está ligado a C2 120 e B4 Escritório está ligado a C4 100; as demais categorias não atendem ao critério.

A categoria é verificada na coluna B; o valor somado vem da coluna C na mesma linha.

Montar a SOMASE

Manter os intervalos alinhados

Os três argumentos precisam cumprir papéis diferentes:

  1. B2:B9 é o intervalo de critérios: nele está a categoria de cada lançamento.
  2. "Escritório" é o critério procurado.
  3. C2:C9 é o intervalo de soma: dele vêm os valores das linhas que atenderam.

Os dois intervalos têm as mesmas oito linhas e começam na linha 2. Assim, uma correspondência em B4 leva exatamente ao valor de C4, e não ao valor de outra linha.

F7 — Total por categoria

Digite esta fórmula em F7, onde fica o indicador Total por categoria.

excel
=SOMASE(B2:B9;"Escritório";C2:C9)
Saída esperada
R$ 220,00

O resultado deve ser exibido com formatação de moeda brasileira.

Detectar um intervalo deslocado

Verifique o alinhamento

Qual representação é inadequada para uma SOMASE por categoria, mesmo que os dois intervalos tenham oito células?

Pratique a SOMASE

Complete a fórmula de total

Em F7, complete a fórmula para calcular o Total por categoria. O resultado deve ser R$ 220,00.

excel
=SOMASE(;"Escritório";)

Passo 4 de 8

Criar um resumo com categoria editável

Centralize o critério em F2

Uma categoria, dois indicadores

Até aqui, os critérios foram escritos diretamente na fórmula, como "Escritório". Isso funciona, mas exige editar duas fórmulas sempre que a categoria mudar.

No resumo, use F2 como a entrada única de categoria. Sem aspas, F2 significa “use o conteúdo desta célula”. Assim, a mesma categoria alimenta a contagem em F6 e o total em F7.

Atenção: "F2" é apenas o texto F2; não é uma referência à célula.

F2 alimenta os dois resultados

Diagrama de planilha em que a célula F2, usada como entrada de categoria, aponta para os indicadores de contagem em F6 e total em F7.

A referência F2 reaproveita a categoria informada em uma única célula, sem mudar os intervalos da base.

Use a referência nas duas funções

Atualize as fórmulas do resumo

No seu arquivo, mantenha F2 com o valor Escritório. Em seguida, substitua o critério literal das fórmulas pelos endereços de célula, sem aspas.

Os intervalos continuam os mesmos: B2:B9 é o intervalo de critérios e C2:C9 é o intervalo de soma. Só o critério passa a vir de F2.

Fórmulas vinculadas à categoria

Digite estas fórmulas nas células indicadas da aba Despesas.

Excel/Calc
F6
=CONT.SE(B2:B9;F2)

F7
=SOMASE(B2:B9;F2;C2:C9)
Saída esperada
F6: 2
F7: 220

Com F2 contendo Escritório, formate F7 como moeda brasileira para exibir R$ 220,00.

Evite transformar a referência em texto

Referência ou texto?

Verdadeiro ou falso: em =CONT.SE(B2:B9;"F2"), o critério usa o conteúdo que estiver na célula F2.

Altere apenas F2 e confira

Teste a categoria editável

Complete o resumo depois de trocar o conteúdo de F2 para Alimentação. Preencha as referências e os resultados exibidos.

Excel/Calc
F2: Alimentação

F6: =CONT.SE(B2:B9;) → 
F7: =SOMASE(B2:B9;;C2:C9) → R$ 

Passo 5 de 8

Aplicar critérios numéricos

Use critérios numéricos para contar e somar despesas por valor, distinguindo comparações estritas e inclusivas.

Localizar valores exatos e comparar números

Igualdade numérica

Agora o critério pode ser um número da coluna Valor. Para localizar despesas de valor exatamente igual a 40, use C2:C9 como intervalo de critérios e informe o número sem aspas:

=CONT.SE(C2:C9;40)

Cada célula de C2:C9 que contém exatamente 40 contribui com 1 para a contagem, independentemente da categoria. Os dois valores 40 da base estão em linhas diferentes e ambos atendem ao critério.

Quais valores são exatamente 40?

Representação da aba Despesas em que C3 e C7, ambos com valor 40, estão contornados e identificados como Atende; os demais valores estão identificados como Não atende.

Com o critério 40, C3 e C7 são as duas correspondências.

Operadores para critérios numéricos

Quando há um operador, ele e o limite formam um único critério entre aspas. Já a igualdade por um número exato pode usar somente o número, como 40.

Escolha o critério que corresponde à pergunta feita sobre os valores.
OperadorSignificadoExemplo de critério
=igual a40
>acima de">100"
<abaixo de"<100"
>=maior ou igual a">=100"
<=menor ou igual a"<=100"
<>diferente de"<>0"

Contar e somar acima do limite

Critério fixo acima de 100

Para verificar valores acima do limite fixo de 100, use ">100" como critério. Em F8, a CONT.SE informa quantos valores atendem. Em F9, a SOMASE calcula o total desses valores.

A condição incide sobre os próprios valores da coluna C. Por isso, C2:C9 é o intervalo de critérios e também o intervalo de soma. O valor exatamente igual a 100 não atende a ">100": acima do limite significa estritamente maior que 100.

O que muda com >=?

Comparação entre os critérios maior que 100 e maior ou igual a 100: 120 atende aos dois; 100 só atende ao segundo.

O sinal de igual inclui o valor que está exatamente no limite.

Fórmulas para o limite fixo

Digite as fórmulas nos indicadores acima do limite. Formate F9 como moeda brasileira, se necessário.

Excel/Calc
F8: =CONT.SE(C2:C9;">100")
F9: =SOMASE(C2:C9;">100";C2:C9)
Saída esperada
F8: 1
F9: R$ 120,00

F9 aparece como R$ 120,00 quando a célula está formatada como moeda brasileira.

Prática: escrever o critério adequado

Construir critérios de comparação

Associe cada pergunta ao critério que deve ser usado no intervalo C2:C9.

Toque em um item e depois no par correspondente.

Desafio: completar os indicadores

Conferir o limite estrito e o inclusivo

Complete a conferência dos indicadores acima do limite fixo e, depois, informe os resultados do teste inclusivo.

Excel/Calc
F8: =CONT.SE(C2:C9;)  →  
F9: =SOMASE(C2:C9;;C2:C9)  →  R$ 

Teste inclusivo: use maior ou igual a 100 nas duas fórmulas.
Contagem  →  
Total  →  R$ 

Passo 6 de 8

Vincular a comparação a um limite editável

Use a concatenação com & para ligar o operador de comparação ao limite editável em F3 e atualizar CONT.SE e SOMASE sem reescrever as fórmulas.

Monte um critério que acompanha F3

Separar regra e limite

Até aqui, o limite estava fixo dentro do critério, como ">100". Para tornar o resumo editável, mantenha a regra > entre aspas e una-a ao número digitado em F3 com &.

Assim, se F3 contém 100, o critério ">"&F3 passa a representar ">100". O operador é a regra fixa; o conteúdo de F3 é o limite que pode mudar.

Como o critério dinâmico é formado

Diagrama de planilha em que o operador maior que entre aspas é unido, com &, ao valor 100 da célula F3 para formar o critério maior que 100 usado em dois indicadores.

O mesmo limite em F3 alimenta a contagem e o total.

Aplique o mesmo critério dinâmico

Use o limite nas duas fórmulas

Na área de resumo, substitua os critérios fixos das células F8 e F9. As duas fórmulas usam C2:C9 como intervalo de critérios; a SOMASE repete esse intervalo como intervalo de soma porque a condição também incide sobre os valores.

Com F3 igual a 100, somente o valor 120 está estritamente acima do limite. O valor 100 não entra, pois “acima do limite” significa >.

Fórmulas dos indicadores acima do limite

Digite estas fórmulas na aba Despesas, mantendo F3 com o valor 100.

Excel/Calc
F8: =CONT.SE(C2:C9;">"&F3)
F9: =SOMASE(C2:C9;">"&F3;C2:C9)
Saída esperada
F8: 1
F9: R$ 120,00

Formate F9 como moeda brasileira para exibir o resultado monetário.

Confira a atualização sem editar fórmulas

Atenção

Atenção às aspas e ao &

Não escreva ">F3": entre aspas, F3 é apenas a letra F seguida do número 3, e não uma referência de célula.

A forma correta deixa as aspas fecharem logo depois do operador e usa & para juntar esse operador ao número existente na célula.

Exemplo

Teste a atualização

No seu arquivo, altere somente F3 de 100 para 80. Não reescreva as fórmulas.

A contagem deve passar para 2 e o total para R$ 220,00, pois entram R$ 120,00 e R$ 100,00. O valor de R$ 80,00 não entra: “acima do limite” significa >, e não >=.

Desafio: atualize os dois indicadores

Aplique o limite de 60

Com F3 definido como 60, complete os critérios dinâmicos, os resultados atualizados e a conferência do valor que está exatamente no limite.

Excel/Calc
F3: 60
F8: =CONT.SE(C2:C9;) → 
F9: =SOMASE(C2:C9;;C2:C9) → R$ 

R$ 60,00 atende à condição? 

Passo 7 de 8

Interpretar um resultado igual a zero

Quando zero não significa ausência

Dois indicadores para ler o zero

Um total igual a zero não responde sozinho se houve lançamentos. Para interpretar o resultado, leia os dois indicadores do mesmo critério:

  • A contagem mostra quantas células da categoria atendem ao critério.
  • O total mostra a soma dos valores dessas linhas.

Na aba Despesas, mantenha as fórmulas do resumo vinculadas a F2:

=CONT.SE(B2:B9;F2)

=SOMASE(B2:B9;F2;C2:C9)

Assim, a contagem informa se a categoria foi encontrada; o total informa quanto os valores correspondentes somam.

Mesma soma, evidências diferentes

Diagrama da planilha comparando Ajustes, que corresponde a uma linha de valor zero, com Viagem, que não corresponde a nenhuma linha.

O total é R$ 0,00 nos dois casos; a contagem revela se há correspondência.

Como chegar à interpretação correta

Compare a contagem com o total

Compare os resultados depois de alterar apenas F2. As duas fórmulas permanecem iguais; muda somente o critério informado na célula.

Leitura dos dois indicadores com a mesma base de despesas.
Valor em F2Registros que atendemContagemTotalInterpretação
AjustesLinha 9: valor 01R$ 0,00Há lançamento com valor zero
ViagemNenhum0R$ 0,00Não há lançamento nessa categoria

Exemplo

Confirme a evidência na base

A contagem positiva confirma que existe ao menos uma célula correspondente no intervalo de critérios. Ela não dispensa a conferência: verifique as linhas encontradas, seus valores e se o intervalo de soma C2:C9 está alinhado ao intervalo de critérios B2:B9.

Por exemplo, em Ajustes, a linha 9 atende ao critério e seu valor é 0; por isso a contagem é 1, embora o total seja R$ 0,00. Em Viagem, não há linha que atenda; por isso ambos os resultados são zero.

Verifique sua interpretação

Interprete os pares de resultados

Com as fórmulas de contagem e total por categoria, F2 é alterada primeiro para Ajustes e depois para Viagem. Os pares observados são, respectivamente, contagem 1 e total R$ 0,00; contagem 0 e total R$ 0,00.

Qual conclusão interpreta corretamente os dois casos?

Passo 8 de 8

Aplicar e conferir o resumo completo

Conclua o resumo de despesas com indicadores por categoria e acima de um limite, testando e justificando cada resultado diretamente na base.

Montar os quatro indicadores

Resumo com duas entradas

Mantenha a base na aba Despesas e as duas entradas editáveis: F2 para a categoria e F3 para o limite. O resumo reúne quatro indicadores, mas eles respondem a duas perguntas independentes:

  • F6 e F7 verificam a categoria informada em F2 no intervalo B2:B9.
  • F8 e F9 verificam quais valores de C2:C9 estão estritamente acima de F3.

Portanto, os indicadores acima do limite não filtram a categoria, e os indicadores por categoria não usam o limite. Comece com F2 igual a Escritório e F3 igual a 100.

Dois critérios, duas leituras da base

Diagrama da planilha Despesas: F2 aponta para correspondências de categoria na coluna B, enquanto F3 aponta separadamente para valores acima do limite na coluna C.

Cada par de indicadores tem seu próprio critério: categoria em F2 ou limite em F3.

Fórmulas finais do resumo

Digite as fórmulas nas células indicadas. Com as entradas iniciais F2=Escritório e F3=100, confira a saída mostrada abaixo.

Excel/Calc
F6: =CONT.SE(B2:B9;F2)
F7: =SOMASE(B2:B9;F2;C2:C9)
F8: =CONT.SE(C2:C9;">"&F3)
F9: =SOMASE(C2:C9;">"&F3;C2:C9)
Saída esperada
F6: 2
F7: R$ 220,00
F8: 1
F9: R$ 120,00

Resultados esperados somente para as entradas iniciais F2=Escritório e F3=100.

Revisar o critério dinâmico

Completar o total acima do limite

Complete o critério da fórmula de F9 para totalizar os valores estritamente acima do limite editável.

Excel/Calc
=SOMASE(C2:C9;;C2:C9)

Testar e conferir os resultados

Aplicação final: testar e justificar

Na sua planilha local, mantenha as quatro fórmulas e faça duas verificações, sem alterar os intervalos nem as fórmulas:

  1. Defina F2 como Transporte e F3 como 40.
  2. Depois, defina F2 como Alimentação e F3 como 60.

Para cada situação, registre os quatro resultados na ordem dos indicadores de F6:F9. Em seguida, informe quais linhas da base justificam o par por categoria e quais linhas justificam o par acima do limite. Lembre-se de que um valor exatamente igual ao limite não atende a >.

Escreva pelo menos 100 caracteres (0/100).

Consolidar a prática

Resumo

Síntese da competência

Você concluiu um resumo de despesas atualizável no Excel ou no Calc.

  • Use CONT.SE quando a pergunta pede contagem de ocorrências e SOMASE quando pede total de valores.
  • Em SOMASE, mantenha o intervalo de critérios e o intervalo de soma alinhados nas mesmas linhas.
  • Use F2 como critério de categoria e ">"&F3 como critério editável acima do limite.
  • Confira cada indicador diretamente nos registros que atendem ao seu próprio critério.
  • Não misture os indicadores por categoria com os indicadores acima do limite: eles respondem a perguntas separadas.

Baixe o Aplicativo agora para ter acesso a + de 5000 cursos gratuitos, exercícios, certificado e muito conteúdo sem pagar nada!

  • Cursos online 100% gratuitos do início ao fim

    Milhares de cursos online em vídeo, ebooks e áudiobooks.

  • Mais de 60 mil exercícios gratuitos

    Para testar seus conhecimentos no decorrer dos cursos online

  • Certificado Digital gratuito válido em todo o Brasil

    Gerado diretamente na galeria de fotos do seu celular e enviado ao seu e-mail

Aplicativo Cursa na tela de ebook, na tela de curso em vídeo e na tela de exercícios do curso, mais o certificado de conclusão de curso