Fórmula de Excel: contar las ocurrencias en todo el libro -

Tabla de contenido

Fórmula genérica

=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!"&rng),criteria))

Resumen

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

=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!A1:Z10000"),B5))

donde "hojas" es el rango con nombre B8: B10.

Contexto

Este libro de trabajo 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 usando en la fórmula, A1: Z10000, es arbitrario y debe ajustarse para adaptarse a sus datos.

Explicación

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

"'"&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 todos estos son 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 COUNTIF tres rangos separados, obtenemos tres resultados en una matriz como esta:

(5;6;5)

Cada artículo es un recuento de una hoja.

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

Articulos interesantes...