Eu tenho uma planilha na qual primeiro aplico um filtro a uma determinada coluna para mostrar "Somente registros exclusivos". Agora tenho uma coluna que contém, por exemplo, valores como:
Applepie
Applesauce
Crabapple
Banana
Mango
O que preciso é a contagem de todas as células visíveis que contenham a palavra "Apple". Observe que isso deve excluir as células ocultas (filtradas).
Agora descobri uma fórmula:
`=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!A:A,ROW(Sheet1!A:A)-MIN(ROW(Sheet1!A:A)),,1))*(Sheet1!A:A="<Text to search>"))`
No entanto, o problema com este é que esta fórmula procura o texto inteiro. NÃO apenas uma parte disso. Como modifico esta fórmula para retornar a contagem de célulascontendo "maçã"ou seja, para este exemplo, ele deve retornar 3 como resultado.
Responder1
COUNTIFcom curingas funcionará sozinho para contar todas as linhas com "Apple" em algum lugar do texto......mas não está restrito a linhas visíveis e você não pode usar COUNTIFem conjunto com sua SUBTOTALfórmula.
Ajuste sua fórmula assim usando SEARCHa função
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!A:A,ROW(Sheet1!A:A)-MIN(ROW(Sheet1!A:A)),,1)),ISNUMBER(SEARCH("Apple",Sheet1!A:A))+0)
Nota: essa versão pode ser lenta, referenciando a coluna inteira - é melhor restringir os intervalos, se puder
Abordagem alternativa
Se quiser evitar essa fórmula longa então você pode usar uma coluna auxiliar para indicar se cada linha é filtrada ou não, por exemplo na Planilha1 Z2 use esta fórmula copiada
=SUBTOTAL(3,A2)
Agora você pode usar uma COUNTIFSfunção para contar linhas visíveis contendo “Apple”
=COUNTIFS(A:A,"*Apple*",Z:Z,1)
Responder2
Teste: =COUNTIF(A2:A10;"Apple*")mudançarange