SUBTOTAL no Excel: Totais que Respeitam seus Filtros
Aprenda a usar a função SUBTOTAL no Excel para somar apenas linhas visíveis após filtrar, a diferença entre 9 e 109 e truques práticos.
Por Ayoub Farid · · 2 min de leitura
A função SOMA (SUM) calcula todas as linhas, inclusive aquelas que você filtrou. Já a função SUBTOTAL soma apenas as linhas que continuam visíveis na tela. É por isso que ela é a escolha perfeita para calcular totais em qualquer lista com filtros na sua planilha.
No Excel em português, a fórmula se chama SUBTOTAL (assim como em inglês).
Sintaxe
=SUBTOTAL(function_num, ref1, ...)| Função | Inclui linhas ocultadas manualmente | Ignora linhas ocultadas manualmente |
|---|---|---|
MÉDIA (AVERAGE) | 1 | 101 |
NÚM.CARACT/CONT.NÚM (COUNT) | 2 | 102 |
CONT.VALORES (COUNTA) | 3 | 103 |
MÁXIMO (MAX) | 4 | 104 |
MÍNIMO (MIN) | 5 | 105 |
SOMA (SUM) | 9 | 109 |
As duas versões ignoram automaticamente as linhas ocultadas por um filtro. A série 100 também ignora as linhas que você ocultou manualmente (clicando com o botão direito do mouse > Ocultar).
Exemplo
Imagine uma coluna de vendas no intervalo D2:D500. Acima da tabela, você insere a fórmula:
=SUBTOTAL(9,D2:D500)Filtre os dados pela região "Norte" e o valor total mostrará apenas os valores do Norte. Limpe o filtro e ele exibirá a soma de tudo novamente.
Contar linhas visíveis
=SUBTOTAL(3,A2:A500)Essa fórmula mostra exatamente quantas linhas visíveis atendem aos critérios do seu filtro (utilizando o equivalente à função COUNTA).
Ela ignora outros SUBTOTALs
Se uma coluna já tiver linhas intermediárias calculadas com SUBTOTAL para cada grupo, uma fórmula final de total geral com SUBTOTAL pula essas linhas automaticamente. Assim, você nunca corre o risco de duplicar os valores na soma. (O recurso Dados > Subtotal insere essas linhas de grupo de forma automática).
AGREGAR: SUBTOTAL com tratamento de erros
A função AGREGAR (AGGREGATE) é uma evolução direta do SUBTOTAL:
=AGGREGATE(9,7,D2:D500)Aqui, o primeiro argumento 9 representa a soma (SUM), e o segundo argumento 7 instrui o Excel a ignorar linhas ocultas e também células com erros.
As Tabelas fazem isso por você
Se você formatar seus dados como uma tabela oficial do Excel (usando o atalho Ctrl+T ou Ctrl+Alt+T dependendo da versão), basta ir na guia Design da Tabela > Linha de Totais. O Excel adicionará fórmulas de SUBTOTAL automaticamente para você.
Perguntas frequentes
Como somar apenas células visíveis no Excel?
Use a fórmula =SUBTOTAL(9,range) (ou 109 para ignorar também as linhas ocultadas manualmente na planilha).
Qual é a diferença entre SUBTOTAL 9 e 109?
Ambos ignoram linhas escondidas por filtros comuns; porém, o número 109 também ignora as linhas que você ocultou manualmente com o botão direito.
Por que a função SOMA não muda quando eu aplico um filtro?
A função SUM (SOMA) inclui todas as células do intervalo informado, mesmo as que estão ocultas. Para que o cálculo considere apenas o que está visível, use a fórmula SUBTOTAL.
A função SUBTOTAL funciona no Planilhas Google (Google Sheets)?
Sim, o Planilhas Google (Google Sheets) suporta a função exatamente da mesma forma e utiliza a mesma numeração de parâmetros.