EXCEL: Cuente filas visibles solo con criterios

Para contar las filas visibles solo con criterios, puede usar una fórmula bastante compleja basada en SUMAPRODUCTO, SUBTOTALES y DESREF. En el ejemplo que se muestra, la fórmula en C12 es:

=SUMAPRODUCTO((C5:C8=C10)*(SUBTOTALES(103,DESREF(C5,FILA(C5:C8)-MIN(FILA(C5:C8)),0))))

Prefacio

La función SUBTOTALESES puede generar fácilmente sumas y recuentos para filas ocultas y no ocultas. Sin embargo, no puede manejar criterios como CONTAR.SI o SUMAR.SI sin alguna ayuda. Una solución es usar SUMAPRODUCTO para aplicar tanto la función SUBTOTALESES (a través de DESREF) como los criterios. Los detalles de este enfoque se describen a continuación.

Cómo funciona esta fórmula

En esencia, esta fórmula funciona al configurar dos matrices dentro de SUMAPRODUCTO. La primera matriz aplica criterios y la segunda matriz maneja la visibilidad:

=SUMAPRODUCTO(criteria*visibility)

El criterio se aplica con parte de la fórmula:

=(C5:C8=C10)

Lo que genera una matriz como esta:

{FALSO;VERDADERO;FALSO;VERDADERO}

Donde VERDADERO significa "cumple con los criterios". Tenga en cuenta que estamos usando la multiplicación (*) en esta matriz, los valores VERDADERO FALSO se convertirán automáticamente a 1 y 0 por la operación matemática, por lo que terminamos con:

{0;1;0;1}

El filtro de visibilidad se aplica utilizando SUBTOTALES, con la función número 103.

SUBTOTALESESES puede excluir filas ocultas al ejecutar cálculos, por lo que podemos usarlo en este caso para generar un "filtro" para excluir filas ocultas dentro de SUMAPRODUCTO. Sin embargo, el problema es que SUBTOTALESESES devuelve un solo número, mientras que necesitamos una matriz de resultados para usarlo con éxito dentro de SUMAPRODUCTO. El truco es usar DESREF para alimentar a SUBTOTALESESES una referencia por fila, de modo que DESREF devolverá un resultado por fila.

Por supuesto, eso requiere otro truco, que es darle a DESREF una matriz que contiene un número por fila, comenzando con cero. Hacemos eso con una expresión construida en la función FILA:

=FILA(C5:C8)-MIN(FILA(C5:C8)

que generará una matriz como esta:

{0;1;2;3}

En resumen, la segunda matriz (que maneja la visibilidad usando SUBTOTALES), se genera así:

=SUBTOTALES(103,DESREF(C5,FILA(C5:C8)-MIN(FILA(C5:C8)),0))
=SUBTOTALES(103,DESREF(C5,{0;1;2;3},0))
=SUBTOTALES(103,{"East";"West";"Midwest";"West"})
={1;0;1;1}

Y, finalmente, tenemos:

=SUMAPRODUCTO({0,1,0,1}*{1;0;1;1})

Que devuelve 1.

Criterios múltiples

Puede extender la fórmula para manejar múltiples criterios como este:

=SUMAPRODUCTO((rng1=criteria1)*(rng2=criteria2)*(SUBTOTALES(103,DESREF(rng,rows,0,1))))

Resumen de resultados

Para devolver una suma de valores en lugar de un recuento, puede adaptar la fórmula para incluir un rango de suma:

=SUMAPRODUCTO(criteria*visibility*sumrange)

Los criterios y las matrices de visibilidad funcionan de la misma manera que se explicó anteriormente, excluyendo las celdas que no son visibles. Si necesita una coincidencia parcial, puede construir una expresión usando ESNUMERO + HALLAR, como se explica aquí.