Fórmula genérica
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Resumen
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 SUBSTITUTE, LEN y SUMPRODUCT. En el ejemplo que se muestra, la fórmula en C11 es:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Nota: La fórmula de esta página cuenta las 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.
Explicación
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 en el rango, SUBSTITUTE quita la subcadena del texto original y LEN calcula la longitud del texto sin la subcadena. Luego, este número se resta de la longitud del texto original. El resultado es el número de caracteres eliminados por SUBSTITUTE.
Luego, la cantidad 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 que se eliminó del texto original, sabemos que la subcadena / palabra apareció dos veces en el texto original.
Debido a que el cálculo anterior está incluido en la función SUMPRODUCT, el resultado es una matriz que contiene un número para cada celda del rango. Estos números representan el número de apariciones de la subcadena en cada celda. Para este ejemplo, la matriz se ve así: (1; 1; 0; 1)
Finalmente, SUMPRODUCT suma todos los elementos de la matriz para obtener el total de apariciones de subcadenas en el rango de celdas.
Ignorando el caso
SUBSTITUTE es una función que distingue entre mayúsculas y minúsculas, por lo que coincidirá con las 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 MAYÚS dentro de SUBSTITUTO para convertir el texto a mayúsculas antes de ejecutar la sustitución:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
