Fórmula genérica
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Resumen
Para contar filas visibles solo con criterios, puede usar una fórmula bastante compleja basada en SUMPRODUCT, SUBTOTAL y OFFSET. En el ejemplo que se muestra, la fórmula en C12 es:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Prefacio
La función SUBTOTAL puede generar fácilmente sumas y recuentos para filas ocultas y no ocultas. Sin embargo, no puede manejar criterios como COUNTIF o SUMIF sin ayuda. Una solución es utilizar SUMPRODUCT para aplicar tanto la función SUBTOTAL (mediante OFFSET) como los criterios. Los detalles de este enfoque se describen a continuación.
Explicación
En esencia, esta fórmula funciona configurando dos matrices dentro de SUMPRODUCT. La primera matriz aplica criterios y la segunda matriz maneja la visibilidad:
=SUMPRODUCT(criteria*visibility)
Los criterios se aplican con parte de la fórmula:
=(C5:C8=C10)
Lo que genera una matriz como esta:
(FALSE;TRUE;FALSE;TRUE)
Donde VERDADERO significa "cumple con los criterios". Tenga en cuenta que debido a que estamos usando la multiplicación (*) en esta matriz, los valores VERDADERO FALSO se convertirán automáticamente en 1 y 0 mediante la operación matemática, por lo que terminamos con:
(0;1;0;1)
El filtro de visibilidad se aplica mediante SUBTOTAL, con la función número 103.
SUBTOTAL es capaz de 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 SUMPRODUCT. Sin embargo, el problema es que SUBTOTAL devuelve un solo número, mientras que necesitamos una matriz de resultados para usarlo con éxito dentro de SUMPRODUCT. El truco consiste en utilizar OFFSET para alimentar SUBTOTAL una referencia por fila, de modo que OFFSET devolverá un resultado por fila.
Por supuesto, eso requiere otro truco, que es darle a OFFSET una matriz que contenga un número por fila, comenzando con cero. Hacemos eso con una expresión construida sobre la función FILA:
=ROW(C5:C8)-MIN(ROW(C5:C8)
que generará una matriz como esta:
(0;1;2;3)
En resumen, la segunda matriz (que maneja la visibilidad usando SUBTOTAL), se genera así:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
Y, finalmente, tenemos:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Que devuelve 1.
Varios criterios
Puede extender la fórmula para manejar múltiples criterios como este:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Sumar resultados
Para devolver una suma de valores en lugar de un recuento, puede adaptar la fórmula para incluir un rango de suma:
=SUMPRODUCT(criteria*visibility*sumrange)
Las matrices de criterios y 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 ISNUMBER + SEARCH, como se explica aquí.
