Capítulo 17 · 17.18
Somar apenas as linhas filtradas, também com uma condição adicional
Filtra uma lista de vendas por uma categoria, mas o total por baixo continua a mostrar o valor de todas as linhas. Isso está correto: SOMA também soma as linhas que o filtro oculta. Se pretende somar apenas o que vê, utilize SUBTOTAL. E se, dentro das linhas filtradas, pretender ainda uma condição adicional, ou ignorar valores de erro, existem mais duas fórmulas úteis.
Como exemplo, utilizamos a lista de vendas da Bloemisterij Het Zonnetje: em A a data, em B o produto, em C a categoria e em D o valor, da linha 2 até à linha 300. Existe um filtro na linha de cabeçalho (consulte Classificar e Filtrar).
Onde colocar as fórmulas?
Não por baixo da lista: uma fórmula diretamente por baixo da lista pode ficar ela própria oculta ao filtrar, e deslocar-se ao ordenar. Mas também não simplesmente ao lado da lista: um filtro oculta linhas inteiras, por isso uma fórmula em F3 desaparece também se a linha 3 for filtrada. Coloque-as, por isso, na linha de cabeçalho (linha 1, ao lado dos títulos), ou numa folha de cálculo separada. No exemplo, estão na linha 1, a partir da coluna F.
A soma das linhas visíveis
=SUBTOTAL(109;D2:D300)
O primeiro número escolhe o cálculo. 9 é a soma, 109 também, com uma diferença: 109 ignora também as linhas que ocultou manualmente (botão direito do rato, Ocultar). 9 ignora apenas as linhas que o filtro oculta. Utilize, portanto, 109 se pretende somar o que vê.
| Cálculo | Filtradas | Também ocultas manualmente |
|---|---|---|
| Soma | 9 | 109 |
| Número de células preenchidas | 3 | 103 |
| Ponto médio | 1 | 101 |
| Máximo | 4 | 104 |
O número de linhas visíveis é contado com =SUBTOTAL(103;B2:B300).

Visível e com uma condição adicional
Filtrou pela categoria Boeketten e pretende somar, dentro dela, apenas os arranjos fúnebres. SOMA.SE.S não tem em conta o filtro e SUBTOTAL não aceita condições. Na internet encontra para isso uma fórmula com SOMARPRODUTO, SUBTOTAL e DESLOCAMENTO. No Euro-Office, essa fórmula devolve o valor de erro #NUM!. Com uma coluna auxiliar já funciona, e de forma mais simples:
- Crie ao lado da lista uma coluna Zichtbaar (visível), aqui a coluna E. Selecione E2:E300, escreva =SUBTOTAL(103;D2) e prima Ctrl+Enter. Cada linha recebe um 1 enquanto estiver visível, e um 0 quando o filtro a oculta.
- Some com SOMA.SE.S e inclua a coluna auxiliar como condição adicional: =SOMA.SE.S(D2:D300;B2:B300;"Rouwstuk";E2:E300;1).
Se alterar o filtro, a coluna auxiliar e a soma são recalculadas de imediato.
Ignorar valores de erro
Se existir algures na coluna um #N/A ou #DIV/0!, SOMA e SUBTOTAL também devolvem um valor de erro. AGREGAR consegue ignorar erros:
=AGREGAR(9;7;D2:D300)
O 9 é novamente a soma, o 7 significa: ignorar as linhas ocultas e os valores de erro.
Armadilhas
- Colunas ocultas são incluídas no cálculo: SUBTOTAL tem em conta apenas as linhas ocultas.
