Você já aplicou um filtro numa tabela grande para ver só as vendas de uma região específica, olhou o total ali embaixo e percebeu que o número não bateu com o que você esperava? Isso acontece porque a função SOMA comum não sabe que existe um filtro ativo — ela soma todas as linhas do intervalo, inclusive as que estão ocultas pelo filtro, e o total acaba incluindo dados que você nem está vendo na tela.
Esse tipo de erro silencioso é perigoso justamente porque não gera nenhum aviso: o número aparece, parece plausível, e só depois — às vezes só quando alguém questiona o relatório — é que você percebe que o total estava contando linhas escondidas. Em relatórios financeiros ou apresentações para gestores, esse tipo de deslize custa credibilidade.
Neste artigo iremos mostrar como a função SUBTOTAL resolve exatamente esse problema, calculando totais que respeitam o filtro ativo, e como escolher entre os dois grupos de números de função (1-11 e 101-111) dependendo de como as linhas estão ocultas na sua planilha.
O que é a função SUBTOTAL no Excel
SUBTOTAL é uma função que calcula um agregado (soma, média, contagem, máximo, mínimo, entre outros) mas, diferente de SOMA ou MÉDIA, ela ignora linhas ocultas por filtro automaticamente. O nome é o mesmo em português e em inglês: SUBTOTAL. A sintaxe é:
=SUBTOTAL(núm_função; intervalo1; [intervalo2]; ...)
- núm_função: um número de 1 a 11 (ou de 101 a 111) que define qual operação a função vai executar — soma, média, contagem etc.
- intervalo1, intervalo2…: os intervalos de células sobre os quais o cálculo será feito. Você pode informar vários.
A grande diferença para SOMA, MÉDIA e afins é justamente essa: a SUBTOTAL “enxerga” quais linhas estão escondidas pelo filtro da tabela e as exclui automaticamente do cálculo, sem que você precise fazer nada além de escolher o número de função certo.
Os números de função: o que cada um faz
O primeiro argumento da SUBTOTAL é o que define a operação. Os mais usados no dia a dia são:
- 1 — MÉDIA
- 2 — CONT.NÚM (conta células com número)
- 3 — CONT.VALORES (conta células não vazias)
- 4 — MÁXIMO
- 5 — MÍNIMO
- 9 — SOMA
- 11 — VAR.P (variância populacional)
Ou seja, =SUBTOTAL(9;C2:C50) soma o intervalo C2:C50, e =SUBTOTAL(1;C2:C50) calcula a média do mesmo intervalo — sempre respeitando o filtro ativo na tabela.
A diferença entre 1-11 e 101-111
Aqui está o detalhe técnico que mais gera confusão: cada número de função de 1 a 11 tem um “gêmeo” de 101 a 111 (ou seja, 1 e 101 fazem a mesma operação, 9 e 109 também, e assim por diante). A diferença entre os dois grupos não tem nada a ver com a operação em si (soma continua sendo soma nos dois casos) — tem a ver com como as linhas ficaram ocultas:
Os números de 1 a 11 ignoram linhas ocultas por filtro, mas contam normalmente as linhas que você ocultou manualmente (clicando com o botão direito no número da linha e escolhendo “Ocultar”).
Os números de 101 a 111 ignoram tanto as linhas ocultas por filtro quanto as ocultadas manualmente — ou seja, são mais “rigorosos”, excluindo qualquer linha que não esteja visível na tela, seja qual for o motivo.
Na prática, se a sua planilha só usa filtro de tabela (Ctrl+Shift+L ou o botão Filtrar), os dois grupos se comportam exatamente igual, porque nenhuma linha foi ocultada manualmente. A diferença só aparece quando você também oculta linhas “na mão”, além de filtrar. Nesse cenário, se quiser que o total ignore tudo que está fora de vista, use a versão 101-111; se quiser que só o filtro seja respeitado (e linhas ocultadas manualmente continuem entrando na conta), use a versão 1-11.
Exemplo prático: total de vendas com filtro ativo
Imagine uma tabela de vendas por vendedor e região. Depois de aplicar um filtro para mostrar só a região “Sul”, a tabela visível fica assim:
Com o filtro mostrando só as linhas de “Sul” (Ana, Bruno e Diego) e ocultando as de “Norte” (Carla e Elisa), veja a diferença entre as duas fórmulas aplicadas na coluna Venda:
=SOMA(C2:C6) soma todas as linhas do intervalo, mesmo as ocultas pelo filtro — o resultado seria 3200 + 1500 + 2800 + 4100 + 900 = 12.500, mesmo com Norte fora da tela.
=SUBTOTAL(9;C2:C6) soma só as linhas visíveis no filtro atual — o resultado seria 3200 + 1500 + 4100 = 8.800, batendo exatamente com o que está sendo exibido na tela.
Esse é o motivo pelo qual, quando você formata um intervalo como Tabela (Ctrl+T) no Excel e ativa a linha de totais, o Excel automaticamente usa fórmulas SUBTOTAL nessa linha, e não SOMA — para garantir que o total sempre reflita o filtro ativo, sem você precisar lembrar de trocar a fórmula manualmente.
Como inserir a linha de totais automática
Se você trabalha com uma Tabela formatada (Ctrl+T), não precisa nem escrever a fórmula SUBTOTAL manualmente na maioria dos casos: marque a opção Linha de Totais na guia Design da Tabela (ou clique com o botão direito em qualquer célula da tabela e escolha essa opção). O Excel adiciona uma linha ao final com um menu suspenso em cada célula, permitindo escolher Soma, Média, Contagem, Máximo, Mínimo e outras opções — todas geradas automaticamente como fórmulas SUBTOTAL por trás dos panos.
SUBTOTAL com múltiplos intervalos
A função também aceita mais de um intervalo, o que é útil quando os dados que você quer somar não estão em um bloco único e contínuo:
=SUBTOTAL(9;C2:C10;E2:E10)
Essa fórmula soma os dois intervalos ao mesmo tempo (ainda respeitando o filtro), o que evita ter que criar uma fórmula separada para cada bloco de dados e depois somar os resultados.
Cuidado: SUBTOTAL dentro de outro SUBTOTAL
Um detalhe técnico importante: se você tiver várias fórmulas SUBTOTAL na mesma coluna (por exemplo, subtotais por região dentro de uma tabela maior), e usar SUBTOTAL de novo para somar a coluna inteira, o Excel automaticamente ignora os subtotais intermediários para não contar os valores em dobro. Isso é um comportamento interno da função, pensado justamente para relatórios com múltiplos níveis de subtotal — como os que o próprio recurso de Subtotais do Excel (guia Dados > Subtotal) cria automaticamente.
Disponibilidade da função SUBTOTAL
A função SUBTOTAL está disponível desde as versões mais antigas do Excel (2003 em diante), passando por todas as versões até o Excel 365, incluindo Excel Online e Excel para Mac. Ela também existe no Google Planilhas, com o mesmo nome e a mesma lógica de números de função.
Compartilhe ou Comente
Se você curtiu esse artigo aonde mostramos como a função SUBTOTAL calcula totais que respeitam filtros e a diferença entre os números de função 1-11 e 101-111, 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.
Você já teve um total errado em uma planilha filtrada por usar SOMA em vez de SUBTOTAL? Sua tabela usa mais filtro ou mais linhas ocultas manualmente? Conta para nós nos comentários!
Perguntas frequentes
Por que meu total com SOMA não muda quando eu filtro a tabela?
Porque a função SOMA não sabe se existe um filtro ativo — ela sempre soma todas as células do intervalo, inclusive as ocultas pelo filtro. Para que o total se ajuste automaticamente ao filtro, use SUBTOTAL(9;intervalo) no lugar de SOMA.
Qual a diferença entre SUBTOTAL(9;…) e SUBTOTAL(109;…)?
As duas somam o intervalo, mas SUBTOTAL(9;…) ignora apenas linhas ocultas por filtro (contando normalmente linhas ocultadas manualmente), enquanto SUBTOTAL(109;…) ignora tanto as linhas ocultas por filtro quanto as ocultadas manualmente.
A função SUBTOTAL soma linhas ocultadas manualmente com Ctrl+9?
Depende do número de função usado. Com os números de 1 a 11 (como o 9, de soma), linhas ocultadas manualmente continuam entrando no cálculo. Com os números de 101 a 111 (como o 109), essas linhas também são excluídas do resultado.