Fórmula genérica
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Resumen
Para buscar un valor en varias hojas de trabajo en un libro y devolver un recuento, puede usar una fórmula basada en las funciones CONTAR.SI e INDIRECTO. Con alguna configuración preliminar, puede utilizar este enfoque para buscar un valor específico en un libro completo. En el ejemplo que se muestra, la fórmula en C5 es:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Contexto: datos de muestra
El libro de trabajo contiene 4 hojas de trabajo en total. Sheet1 , Sheet2 y Sheet3 contienen cada uno 1000 nombres aleatorios que se ven así:
Explicación
El rango B7: B9 contiene los nombres de las hojas que queremos incluir en la búsqueda. Estas son solo cadenas de texto, y debemos trabajar un poco para que se reconozcan como referencias de hoja válidas.
Trabajando de adentro hacia afuera, esta expresión se usa para construir una referencia de hoja completa:
"'"&B7&"'!"&"1:1048576"
Las comillas simples se agregan para permitir nombres de hoja con espacios, y el signo de exclamación es una sintaxis estándar para rangos que incluyen un nombre de hoja. El texto "1: 1048576" es un rango que incluye todas las filas de la hoja de trabajo.
Una vez que se evalúa B7 y se concatenan los valores, la expresión anterior devuelve:
"'Sheet1'!1:1048576"
que entra en la función INDIRECTA como el argumento 'ref_text'. INDIRECT evalúa este texto y devuelve una referencia estándar a cada celda en Sheet1 . Esto entra en la función CONTAR.SI como rango. El criterio se proporciona como una referencia absoluta a C4 (bloqueado para que la fórmula se pueda copiar en la columna C).
CONTAR.SI luego devuelve un recuento de todas las celdas con un valor igual a "mary", 25 en este caso.
Nota: COUNTIF no distingue entre mayúsculas y minúsculas.
Contiene frente a igual
Si desea contar todas las celdas que contienen el valor en C4, en lugar de todas las celdas iguales a C4, puede agregar comodines a los criterios como este:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Ahora CONTAR.SI contará las celdas con la subcadena "John" en cualquier lugar de la celda.
Actuación
En general, no es una buena práctica especificar un rango que incluya todas las celdas de la hoja de trabajo. Hacerlo puede causar problemas de rendimiento, ya que el rango incluye millones y millones de celdas. En este ejemplo, el problema se agrava, ya que la fórmula usa la función INDIRECTA, que es una función volátil. Las funciones volátiles se recalculan con cada cambio de hoja de trabajo, por lo que el impacto en el rendimiento puede ser enorme.
Cuando sea posible, restrinja los rangos a un tamaño razonable. Por ejemplo, si sabe que los datos no aparecerán después de la fila 1000, puede buscar solo las primeras 1000 filas de esta manera:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
