EXCEL: Contar palabras específicas en un rango

Para contar cuántas veces aparece una palabra específica (o cualquier subcadena) dentro de un rango de celdas, puede usar una fórmula basada en las funciones SUSTITUIR, LARGO y SUMAPRODUCTO. En el ejemplo que se muestra, la fórmula en C11 es:

=SUMAPRODUCTO((LARGO(B5:B8)-LARGO(SUSTITUIR(B5:B8,C2,"")))/LARGO(C2))

Nota: La fórmula en esta página cuenta instancias de una palabra en un rango. Por ejemplo, si una celda contiene dos instancias de una palabra, contribuirá con 2 al recuento total. Si solo desea contar las celdas que contienen una palabra específica, consulte esta fórmula simple basada en la función CONTAR.SI.

Cómo funciona esta fórmula

En la versión genérica de la fórmula, rng representa el rango a verificar, y txt es la palabra o subcadena a contar.
En el ejemplo que se muestra, B5: B8 es el rango para verificar, y C2 contiene el texto (palabra o subcadena) para contar.
Para cada celda del rango, SUSTITUIR elimina la subcadena del texto original y LARGO calcula la longitud del texto sin la subcadena. Este número se resta de la longitud del texto original. El resultado es el número de caracteres que fueron eliminados por SUSTITUIR.
Luego, el número de caracteres eliminados se divide por la longitud de la subcadena. Por lo tanto, si una subcadena o palabra tiene 5 caracteres de longitud y faltan 10 caracteres después de eliminarla del texto original, sabemos que la subcadena / palabra apareció dos veces en el texto original.
Debido a que el cálculo anterior está envuelto en la función SUMAPRODUCTO, el resultado es una matriz que contiene un número para cada celda en el rango. Estos números representan el número de ocurrencias de la subcadena en cada celda. Para este ejemplo, la matriz se ve así: {1; 1; 0; 1}
Finalmente, SUMAPRODUCTO suma todos los elementos de la matriz para obtener las ocurrencias totales de la subcadena en el rango de celdas.

Ignorando el caso

SUSTITUIR es una función que distingue entre mayúsculas y minúsculas, por lo que coincidirá con mayúsculas y minúsculas al ejecutar una sustitución. Si necesita contar las apariciones en mayúsculas y minúsculas de una palabra o subcadena, use la función MAYUSC dentro de SUSTITUIR para convertir el texto a mayúsculas antes de ejecutar la sustitución:

=SUMAPRODUCTO((LARGO(rng)-LARGO(SUSTITUIR((MAYUSC(rng)),MAYUSC(txt),"")))/LARGO(txt))