Contar valores no Excel, mas desconsiderando linhas ocultas pelo filtro

Question

Boa noite!

Procurei em vários locais, fiz várias tentativas, mas não consegui solucionar meu problema.

Tenho uma planilha onde os valores de uma coluna são 1, 2, 3 e 4 e utilizo a função Cont.Se para determinar a quantidade de cada valor anteriormente mencionado. Porém, necessito que esta função seja executada quando aplico o filtro, mas que ignore as células que foram ocultas pelo filtro, como se fosse uma combinação do Cont.Se e do SubTotal juntos.

Alguém poderia contribuir com alguma função simples ou com um código VBA?

Muito grato aos que puderem contribuir comigo e aos que aprenderão com o mesmo problema que o meu.

Answer 1

Eu sei fazer assim:

ActiveSheet.Cells(1, 4) = Range("A2:A21").Cells.SpecialCells(xlCellTypeVisible).Count

Onde A1:A21 é o valor total onde estão os ítens para serem contados, troque por sua range.

e o ActiveSheet.Cells(1,4) é onde vai aparecer o valor no caso linha1 e coluna 4 ou D1.

Aí tem que ver sua planilha para saber como "disparar" este código.

--------------------

Usando a fórmula SUBTOTAL do próprio excel é só fazer assim:   =SUBTOTAL(3;A:A)  ONDE A:A é a range onde irá contar os dados. só trocar.

Se tiver alguma linha preenchida antes dos valores para contar, provavelmente terá, pode se fazer de duas formas, vamos dizer que vai começar os dados do A2, visto que A1 é o nome da coluna.

=SUBTOTAL(3;A:A)-1   (utiliza menos recursos da máquina)

ou

=SUBTOTAL(3;A2:A100000)  (utiliza MAIS recursos da máquina)

Espero ter ajudado.

Answer 2

Bom dia!

Muito grato pelo auxílio, porém, talvez ou não funcionou ou eu não tenha entendido como proceder, pois quando coloco o comando no VB não surti nenhum efeito.

Pode ser que eu tenha solicitado de forma errada, faltando informações. A planilha funcionará da seguinte forma:

Onde tenho o resumo das atividades estou utilizando o cont.se com os critérios definidos e tenho filtro aplicado por área, não sendo o mesmo local da função cont.se, onde cada valor representa um status. O que necessito é, quando aplicar o filtro nas áreas o totalizador (cont.se) do resumo das atividades altere seus valores, de acordo com o que foi selecionado no filtro.

Muito grato se puder ou não ajudar.

Answer 3

Fica muito mais fácil quando você envia a planilha em anexo, se possível faça o upload no sendspace.com e mande o link aqui.

Mas o que eu tentei dizer é o seguinte, a função subtotal conta os valores que estão visíveis em uma range, então eu precisava saber o que o seu cont.se faz (na verdade saber qual a condição SE para ele contar) aí iria implementar o SE com o subtotal e retirar o seu cont.se, entendeu?

mande sua planilha fica extremamente mais fácil de fazer com o modelo em mãos.

Qualquer coisa me envie no email [e-mail]

Answer 4

Boa noite!

Enviei  no seu e-mail, como sugerido.

Havia tentado colar uma imagem do arquivo, porém minha conta ainda não foi validada para tanto.

Muito grato mesmo!

Answer 5

Boa noite!

Enviei  no seu e-mail, como sugerido.

Havia tentado colar uma imagem do arquivo, porém minha conta ainda não foi validada para tanto.

Muito grato mesmo!

Boa noite. Havia enviado o anexo conforme solicitado pelo Bruno e ele fez uma função que funcionou perfeitamente.

=SOMARPRODUTO(SUBTOTAL(3;DESLOC(W$9:W$300;LIN(W$9:W$300)-LIN(W$9);0;1));--(W$9:W$300=4))

Muito grato Bruno, ajudou muito!

Answer 6

Boa noite!

Enviei  no seu e-mail, como sugerido.

Havia tentado colar uma imagem do arquivo, porém minha conta ainda não foi validada para tanto.

Muito grato mesmo!

Boa noite. Havia enviado o anexo conforme solicitado pelo Bruno e ele fez uma função que funcionou perfeitamente.

=SOMARPRODUTO(SUBTOTAL(3;DESLOC(W$9:W$300;LIN(W$9:W$300)-LIN(W$9);0;1));--(W$9:W$300=4))

Muito grato Bruno, ajudou muito!

Oi pessoal, 

Estou com um caso bem similar... Preciso fazer a mesma coisa...

poderia me encaminha a planilha para eu poder entender a função que o amigo Bruno fez? 

Muito obrigado pessoal

Answer 7

Eu preciso de uma fórmula semelhante, mas meus valores estão em texto, ex: contar quantas vezes o valor "Prescrição" aparece no intervalo J:J ignorando as células ocultas. Alguém poderia me ajudar?

Answer 8

Tambem precisei de uma forma de contar linhas após o filtro. 

Usei a funcao SUBTOTAL que é assim: =SUBTOTAL(REF, FAIXA DE CELULAS). Quando se quer CONTAR após um filtro usa-se a referencia 102 para numeros. Ficou assim: =SUBTOTAL(102,A2:A4). Se for contar sem filtro fica: =SUBTOTAL(2,A2:A4). Se voce quiser SOMAR, a referencia é 9 (sem filtro) ou 109 (com filtro).

Para contar celulas com texto mude a referencia para 103.

O help do Excel 2013 na funcao SUBTOTAL tem a tabela completa.

Answer 9

Show de bola essa resposta, era isso que estava procurando... Parabéns Bruno Henry