Número Excel-Count de células visíveis contendo determinada string

Question

Eu tenho uma planilha na qual aplico primeiro um filtro a uma determinada coluna para mostrar "Apenas registros únicos". Agora eu tenho uma coluna que contém, por exemplo, valores como:

Applepie
Applesauce
Crabapple
Banana
Mango

O que eu preciso é a contagem de todas as células visíveis contendo a palavra "Apple". Observe que isso deve excluir as células ocultas (filtradas).
Agora eu 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 é esta fórmula procura o texto inteiro. NÃO é apenas uma parte disso. Como modifico essa fórmula para retornar a contagem de células contendo "Apple" , ou seja, para este exemplo, ele deve retornar 3 como resultado.

    

Answer 1

COUNTIF com 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 COUNTIF em conjunto com sua fórmula SUBTOTAL .

Ajuste sua fórmula assim usando SEARCH function

=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 toda a coluna - melhor restringir os intervalos se você puder

Abordagem alternativa

Se você quiser evitar essa fórmula longa, use uma coluna auxiliar para indicar se cada linha é filtrada ou não, por exemplo, na Folha1 Z2 use esta fórmula copiada para baixo

=SUBTOTAL(3,A2)

Agora você pode usar uma função COUNTIFS para contar linhas visíveis contendo "Apple"

=COUNTIFS(A:A,"*Apple*",Z:Z,1)

    

Answer 2

Teste: =COUNTIF(A2:A10;"Apple*") alteração range