Contar ocurrencias de un texto específico en todo el libro de Excel

Es fácil para nosotros contar cuántas veces aparece un texto o valor numérico específico en un rango de celdas con la función CONTAR.SI. Sin embargo, para contar ocurrencias de un texto específico en todo el libro, se requiere una fórmula más compleja. Este artículo va a demostrar una fórmula basada en las funciones SUMAPRODUCTO, CONTAR.SI e INDIRECTO para resolver este problema.

¿Cómo contar ocurrencias en todo el libro?

Supongamos que hay 4 hojas de trabajo (Hoja2, Hoja3, Hoja4 y Hoja5) en tu libro, y quieres contar cuántas veces aparece el texto específico “Laptop” en estas 4 hojas, puedes hacer lo siguiente para lograrlo.

Fórmula genérica

=SUMAPRODUCTO(CONTAR.SI(INDIRECTO(“‘”&rango1&”‘!”&rango2),criterio))

Argumentos

¿Cómo usar esta fórmula?

1. Primero necesitarás crear una nueva hoja y listar todos los nombres de las hojas en un rango de celdas. En este caso, listo todos los nombres de las hojas en el rango B7:B10.

2. Selecciona una celda en blanco para mostrar el resultado.

3. Introduce una de las siguientes fórmulas en ella y presiona la tecla Enter para obtener el resultado.

Si ya has ingresado el texto específico en una celda (por ejemplo, B3), puedes hacer referencia a esta celda en la fórmula de la siguiente manera:

=SUMAPRODUCTO(CONTAR.SI(INDIRECTO("'"&B7:B10&"'!A1:Z10000"),B3))

O escribe directamente el texto específico entre comillas dobles en la fórmula de esta manera:

=SUMAPRODUCTO(CONTAR.SI(INDIRECTO("'"&B7:B10&"'!A1:Z10000"),"Laptop"))

Nota: Aquí el rango A1:Z10000 es arbitrario, puedes cambiarlo para adaptarlo a tus datos.

¿Cómo funciona esta fórmula?

=SUMAPRODUCTO(CONTAR.SI(INDIRECTO("'"&B7:B10&"'!A1:Z10000"),B3))

• "'"&B7:B10&"'!A1:Z10000": Aquí cada nombre de hoja en la matriz se une mediante concatenación al rango A1:Z10000, y obtendrás una matriz como {"'Hoja2'!A1:Z10000";"'Hoja3'!A1:Z10000";"'Hoja4'!A1:Z10000";"'Hoja5'!A1:Z10000"}.
• INDIRECTO({"'Hoja2'!A1:Z10000";"'Hoja3'!A1:Z10000";"'Hoja4'!A1:Z10000";"'Hoja5'!A1:Z10000"}): La función INDIRECTO hace referencia a los rangos en las cuatro hojas.
• CONTAR.SI(INDIRECTO("'"&B7:B10&"'!A1:Z10000"),B3): La función CONTAR.SI cuenta el número de veces que aparece el valor específico en cada hoja y devuelve una matriz {13;13;13;13}. Esto significa que el texto específico aparece 13 veces en cada hoja.
• SUMAPRODUCTO({13;13;13;13}): La función SUMAPRODUCTO suma todos los números en la matriz y devuelve el resultado final como 52.

Funciones relacionadas

Fórmulas relacionadas