Cuenta Palabras Específicas en un Rango de Celdas con esta Fórmula Maestra

En el universo de Microsoft Excel, existen fórmulas que, por su ingeniosa construcción, se convierten en auténticas herramientas maestras. Una de ellas es la que nos permite contar el número total de veces que aparece una palabra o una cadena de texto específica dentro de un rango de celdas. A diferencia de funciones como CONTAR.SI, que solo cuenta celdas que contienen un valor, esta fórmula es capaz de contar cada aparición de la palabra, incluso si se repite varias veces en la misma celda.

Esta técnica es increíblemente útil para análisis de texto, búsqueda de palabras clave en comentarios, informes y cualquier tarea que requiera una cuantificación precisa de términos específicos en un conjunto de datos.

Sintaxis

=SUMAPRODUCTO((LARGO(rango_texto)-LARGO(SUSTITUIR(MAYUSC(rango_texto);MAYUSC("palabra_a_buscar");"")))/LARGO("palabra_a_buscar"))

A primera vista, la fórmula puede parecer compleja, pero en realidad es una brillante secuencia de funciones anidadas que trabajan en conjunto. Vamos a desglosarla para entender su lógica:

• MAYUSC(…): La fórmula utiliza la función MAYUSC en dos lugares clave: tanto en el rango de texto como en la palabra a buscar. Esto convierte todo el texto a mayúsculas antes de la comparación, asegurando que la búsqueda no distinga entre mayúsculas y minúsculas (por ejemplo, contará «Excel», «excel» y «EXCEL» por igual).
• SUSTITUIR(…): El núcleo de la operación. La función SUSTITUIR toma el texto (ya en mayúsculas) de cada celda del rango y reemplaza todas las apariciones de la «palabra_a_buscar» por una cadena de texto vacía («»).
• LARGO(…): Se usa la función LARGO para medir la longitud del texto. La fórmula calcula dos longitudes: la del texto original y la del texto después de haber eliminado la palabra buscada.
• LARGO(…) – LARGO(SUSTITUIR(…)): Al restar la nueva longitud de la longitud original, obtenemos el número total de caracteres que ocupaban todas las apariciones de la palabra buscada en cada celda.
• / LARGO(«palabra_a_buscar»): Para obtener el número de veces que apareció la palabra, dividimos la diferencia de caracteres por la longitud de la propia palabra. Por ejemplo, si la palabra es «datos» (5 caracteres) y la diferencia de longitud es 15, sabemos que la palabra apareció 3 veces (15 / 5 = 3).
• SUMAPRODUCTO(…): Esta potente función es la que permite que todo el cálculo se aplique a un rango completo de celdas. SUMAPRODUCTO procesa la operación para cada celda del rango, generando una matriz de resultados (el número de apariciones por celda), y finalmente suma todos esos resultados para devolver un único valor total.

Ejemplos

Para ilustrar el uso de esta fórmula, utilizaremos la siguiente tabla de comentarios como nuestro rango de datos (A2:A5).

Ejemplo 1: Conteo básico de una palabra

Queremos contar cuántas veces aparece la palabra «informe» en todo el rango de comentarios.

=SUMAPRODUCTO((LARGO(A2:A5)-LARGO(SUSTITUIR(MAYUSC(A2:A5);MAYUSC("informe");"")))/LARGO("informe"))

Resultado esperado: 2. La fórmula encuentra «informe» en la celda A2 y en la celda A5.

Ejemplo 2: Conteo sin distinción de mayúsculas y minúsculas

Ahora, contaremos la palabra «Análisis», que aparece escrita con mayúscula inicial y con minúscula.

=SUMAPRODUCTO((LARGO(A2:A5)-LARGO(SUSTITUIR(MAYUSC(A2:A5);MAYUSC("Análisis");"")))/LARGO("Análisis"))

Resultado esperado: 3. Gracias a la función MAYUSC, la fórmula cuenta «análisis» en A2, y las dos apariciones de «análisis» en A3, sin importar cómo estén escritas.

Ejemplo 3: Conteo de una palabra que se repite en la misma celda

Contaremos cuántas veces aparece la palabra «datos», que en la celda A2 se encuentra dos veces.

=SUMAPRODUCTO((LARGO(A2:A5)-LARGO(SUSTITUIR(MAYUSC(A2:A5);MAYUSC("datos");"")))/LARGO("datos"))

Resultado esperado: 3. La fórmula cuenta las dos apariciones en A2 y la aparición en A4.

Ejemplo 4: Demostración de la limitación con subcadenas

Un aspecto importante de esta fórmula es que no distingue palabras completas, sino cadenas de texto. Si buscamos «dato», también la encontrará dentro de «candidato» o «datos».

=SUMAPRODUCTO((LARGO(A2:A5)-LARGO(SUSTITUIR(MAYUSC(A2:A5);MAYUSC("dato");"")))/LARGO("dato"))

Resultado esperado: 4. La fórmula cuenta «dato» dentro de «datos» (A2, dos veces), dentro de «candidato» (A4) y dentro de «datos» (A4).

Aplicaciones Prácticas

• 1Análisis de sentimiento o feedback: Contar la frecuencia de palabras como «excelente», «problema», «lento» o «mejora» en una encuesta de satisfacción del cliente para obtener métricas rápidas.
• 2Auditoría de contenido y SEO: Verificar la densidad de una palabra clave (keyword) en un conjunto de textos, como descripciones de productos, artículos de blog o metadatos.
• 3Seguimiento de menciones: En informes o resúmenes de reuniones, contar cuántas veces se menciona un producto, un proyecto o el nombre de un responsable.
• 4Análisis de datos no estructurados: Realizar un conteo rápido de términos específicos en exportaciones de bases de datos, logs de sistema o cualquier volcado de texto.

Observaciones

La principal consideración al usar esta fórmula es su comportamiento con las subcadenas. Como se vio en el Ejemplo 4, la fórmula cuenta apariciones de la cadena de texto buscada, sin importar si forma parte de una palabra más grande. Para contar únicamente palabras completas, se requerirían fórmulas más complejas que busquen la palabra rodeada de espacios o signos de puntuación.

A pesar de esta limitación, su capacidad para realizar un conteo masivo y sensible a múltiples apariciones por celda la convierte en una solución extremadamente eficiente para la mayoría de los casos de análisis de texto en Excel.

Errores comunes

• #¡VALOR!: Este error ocurrirá si alguna de las celdas en el rango_texto contiene un valor de error preexistente (por ejemplo, #N/A, #DIV/0!, etc.). La fórmula no puede procesar celdas con errores.

Disponibilidad por versión de Excel

Todas las funciones utilizadas en esta fórmula (SUMAPRODUCTO, LARGO, SUSTITUIR, MAYUSC) han estado presentes en Excel durante mucho tiempo, lo que garantiza una compatibilidad muy amplia.

Alternativas

• =SUMA((LARGO(rango_texto)-LARGO(SUSTITUIR(MAYUSC(rango_texto);MAYUSC("palabra");"")))/LARGO("palabra"))

  En Microsoft 365, que utiliza matrices dinámicas, la función SUMA puede reemplazar a SUMAPRODUCTO directamente. En versiones anteriores de Excel, esta alternativa solo funciona si se introduce como una fórmula de matriz (presionando Ctrl + Shift + Enter).

• Power Query (Obtener y transformar datos): Para conjuntos de datos muy grandes o para tareas de limpieza y análisis de texto más complejas y recurrentes, Power Query es una alternativa mucho más potente y robusta. Permite dividir columnas por delimitadores, contar palabras y transformar texto de formas muy avanzadas sin necesidad de fórmulas complejas en la hoja de cálculo.

• Scripts de VBA o Office Scripts: Para una automatización completa o lógicas de conteo personalizadas (por ejemplo, contar solo palabras completas ignorando la puntuación), se puede crear una función personalizada utilizando VBA (para escritorio) u Office Scripts (para la versión web y de escritorio en M365).