EXCEL: Contar ocurrencias en todo el libro de trabajo

Para contar coincidencias en todo el libro de trabajo, puede usar una fórmula basada en las funciones CONTAR.SI y SUMAPRODUCTO. En el ejemplo que se muestra, la fórmula en D5 es:

=SUMAPRODUCTO(CONTAR.SI(INDIRECTO("'"&sheets&"'!A1:Z10000"),B5))

donde "hojas" es el rango nombrado B8: B10.

Contexto

Este libro tiene cuatro hojas de trabajo. Tres de las hojas de trabajo, "Hoja1", "Hoja2" y "Hoja3" contienen 1000 nombres aleatorios en una tabla que se ve así:

Nota: el rango que estamos utilizando en la fórmula, A1: Z10000, es arbitrario y debe ajustarse para adaptarse a sus datos.

Cómo funciona esta fórmula

Trabajando de adentro hacia afuera, primero tenemos esta expresión dentro de la función INDIRECTO:

"'"&sheets&"'!A1:Z10000"

Debido a que "hojas" es un rango con nombre que contiene "Hoja1", "Hoja2" y "Hoja3", obtenemos una matriz como esta una vez que se evalúa la expresión:

{"'Sheet1'!A1:Z10000";"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000"}

Dentro de la matriz, tenemos tres valores, y cada uno es un nombre de hoja unido mediante concatenación al rango A1: Z10000. Tenga en cuenta que estos son todos valores de texto.

A continuación, la función INDIRECTO se utiliza para convertir cada valor de texto en una referencia adecuada, que se suministra a la función CONTAR.SI como argumento de rango, junto con el valor en D5 para los criterios.

Como le hemos dado a CONTAR.SI tres rangos separados, obtenemos tres resultados en una matriz como esta:

{5;6;5}

Cada artículo es un recuento de una hoja.

Finalmente, SUMAPRODUCTO se usa para sumar la matriz y devuelve un resultado de 16.