Usando o SUMPRODUCT no Excel com filtros

Question

Estou com problemas ao usar SUMPRODUCT junto com um filtro. Eu tenho a seguinte fórmula:

=SUMPRODUCT(LEN(B3:B7)-LEN(SUBSTITUTE(B3:B7;".";"")))

Eu preciso contar quantos "". estão no intervalo. No entanto, quando eu uso alguns filtros, ele sempre conta todos os "." nesse intervalo, mas eu só quero contar os visíveis.

Normal:

Apósofiltro:

Como posso fazer isso?

    

Answer 1

Adicione uma condição à sua fórmula SUMPRODUCT :

=SUMPRODUCT((LEN(D3:D8)-LEN(SUBSTITUTE(D3:D8,".","")))*SUBTOTAL(103,OFFSET(D$2,ROW($A$1:$A$6),0)))

SUBTOTAL é capaz de ignorar linhas ocultas . Nesse caso, usamos 103 porque retorna 1 para células não vazias, o que é útil. Temos que modificá-lo com a função OFFSET para que ele retorne a contagem como uma matriz em vez de um único valor. O ponto chave está na seleção dos intervalos.

D3:D8 deve ser o intervalo em que você deseja contar o texto.
D$2 deve ser a célula diretamente acima desse intervalo. $A$1:$A$6 pode estar em qualquer coluna, mas deve começar na linha 1 e ter tantas linhas quanto o intervalo em que você está contando o texto. Eu usei A1:A6 apenas por hábito e conveniência. Ele serve apenas para nos fornecer uma variedade de números para alimentar em OFFSET , para que SUBTOTAL funcione bem com SUMPRODUCT .