La función SUBTOTALES con criterios.

Como ya sabemos, la función SUBTOTALES nos permite realizar cálculos con once operaciones distintas sin tomar en cuenta celdas ocultas. Esto es muy práctico cuando usamos Autofiltro

El resultado de la fórmula refleja sólo los valores de las filas visibles. Un lector me pregunta si se puede hacer el cálculo de tal manera que SUBTOTALES sólo tome en cuenta los valores positivos (o negativos, o distintos de cero, es decir, aplicando algún criterio al cálculo).

Una forma de hacerlo sería agregando una columna auxiliar y luego aplicar el filtro a la columna del criterio y a la columna auxiliar. Por ejemplo

También se puede hacer con fórmulas, que es lo que vamos a mostrar en esta nota. La ventaja de hacerlo con fórmulas es que podemos mostrar distintos resultados, por ejemplo el total de los montos positivos y el total de los negativos, en una misma vista de la hoja.

Supongamos que queremos ver el total de los montos positivos en la celda B1 y el total de los negativos en D1

La celda B1 contiene la fórmula

=SUMAPRODUCTO(SUBTOTALES(9,DESREF(B4,FILA($B$4:$B$14)-FILA($B$4),,1))*($B$4:$B$14>0))


La celda D1 contiene la fórmula

=SUMAPRODUCTO(SUBTOTALES(9,DESREF(B4,FILA($B$4:$B$14)-FILA($B$4),,1))*($B$4:$B$14<0))

Al filtrar por el criterio “a”, por ejemplo, veremos

subtcrit04

Analicemos la fórmula:

DESREF(B4,FILA($B$4:$B$14)-FILA($B$4),,1) crea una matriz que contiene los valores del rango B4:B14

La expresión ($B$4:$B$14<0) crea una martiz de valores FALSO o VERDADERO

Al multiplicar ambas expresiones entre sí obtenemos un matriz donde los valores negativos han sido remplazados por ceros

Usamos estas expresiones como argumentos de la función SUBTOTALES con el operador 9 (suma).

La función SUMAPRODUCTO opera en forma matricial, por lo que nos permite realizar todas estas operaciones en una única celda.

Para calcular el subtotal de los valores negativos usamos ($B$4:$B$14<0); si quisiéramos hacer cálculos que no incluya ceros usaríamos ($B$4:$B$14<>0).