Fórmula genérica
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Resumo
Para contar quantas vezes uma palavra específica (ou qualquer substring) aparece dentro de um intervalo de células, você pode usar uma fórmula baseada nas funções SUBSTITUTE, LEN e SUMPRODUCT. No exemplo mostrado, a fórmula em C11 é:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Nota: a fórmula nesta página conta as instâncias de uma palavra em um intervalo. Por exemplo, se uma célula contém duas instâncias de uma palavra, contribuirá com 2 para a contagem total. Se você quiser apenas contar células que contenham uma palavra específica, consulte esta fórmula simples baseada na função CONT.SE.
Explicação
Na versão genérica da fórmula, rng representa o intervalo a ser verificado e txt é a palavra ou substring a ser contada.
No exemplo mostrado, B5: B8 é o intervalo a ser verificado e C2 contém o texto (palavra ou substring) a ser contado.
Para cada célula no intervalo, SUBSTITUTE remove a substring do texto original e LEN calcula o comprimento do texto sem a substring. Esse número é então subtraído do comprimento do texto original. O resultado é o número de caracteres que foram removidos por SUBSTITUTE.
Em seguida, o número de caracteres removidos é dividido pelo comprimento da substring. Portanto, se uma substring ou palavra tiver 5 caracteres de comprimento e houver 10 caracteres faltando depois de removida do texto original, sabemos que a substring / palavra apareceu duas vezes no texto original.
Como o cálculo acima é agrupado na função SUMPRODUCT, o resultado é uma matriz que contém um número para cada célula no intervalo. Esses números representam o número de ocorrências da substring em cada célula. Para este exemplo, a matriz se parece com isto: (1; 1; 0; 1)
Por fim, SUMPRODUCT soma todos os itens na matriz para obter o total de ocorrências de substring no intervalo de células.
Ignorando maiúsculas e minúsculas
SUBSTITUTE é uma função que faz distinção entre maiúsculas e minúsculas, portanto, corresponderá a maiúsculas e minúsculas ao executar uma substituição. Se você precisar contar as ocorrências em maiúsculas e minúsculas de uma palavra ou substring, use a função UPPER em SUBSTITUTE para converter o texto em maiúsculas antes de executar a substituição:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
