Fórmula genérica
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,A1)))
Resumo
Para contar o número de palavras ou palavras-chave específicas que aparecem em uma determinada célula, você pode usar uma fórmula baseada nas funções SEARCH, ISNUMBER e SUMPRODUCT. No exemplo mostrado, a fórmula em C5 é:
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,B5)))
onde "palavras-chave" é o intervalo nomeado E5: E9.
Explicação
Observação: se uma palavra-chave aparecer mais de uma vez em uma determinada célula, ela será contada apenas uma vez. Em outras palavras, a fórmula só conta instâncias de palavras-chave diferentes.
O núcleo desta fórmula é a abordagem ISNUMBER + SEARCH para localizar texto em uma célula, que é explicada com mais detalhes aqui. Neste caso, procuramos em cada célula todas as palavras no intervalo nomeado "palavras-chave" (E5: E9). Fazemos isso passando o intervalo para SEARCH como o argumento find_text. Porque passamos em uma matriz de 5 itens:
("green";"orange";"white";"blue";"pink")
obtemos uma matriz de 5 itens de volta como resultado:
(#VALUE!;#VALUE!;1;#VALUE!;14)
Os números correspondem às correspondências e o #VALUE! erro significa que nenhuma correspondência foi encontrada. Nesse caso, como não nos importamos onde o texto foi encontrado na célula, usamos ISNUMBER para converter a matriz em valores TRUE e FALSE:
(FALSE;FALSE;TRUE;FALSE;TRUE)
E o duplo negativo (-) para transformá-los em 1s e zeros:
(0;0;1;0;1)
A função SUMPRODUCT então simplesmente retorna a soma do array, 2 neste caso.
Tratamento de palavras-chave vazias
Se o intervalo de palavras-chave contiver células vazias, a fórmula não funcionará corretamente, pois a função SEARCH retorna zero ao procurar por uma string vazia (""). Para filtrar quaisquer células vazias no intervalo de palavras-chave, você pode usar a variação abaixo:
(=SUMPRODUCT(--ISNUMBER(SEARCH(IF(keywords"",keywords),B5))))
Observação: esta versão é uma fórmula de matriz e deve ser inserida com control + shift + enter.
