Fórmula SUBTOTALES SI – Excel y Google Sheets

Este tutorial demostrará cómo calcular el «subtotales si», realizando la Función SUBTOTALES, sólo en las celdas que cumplen ciertos criterios.

 

Función SUBTOTALES

La Función SUBTOTALES se utiliza para realizar varios cálculos sobre un rango de datos (conteo, suma, promedio, etc.). Si no está familiarizado con la función, se preguntará por qué no utilizaría simplemente las funciones CONTAR, SUMA o PROMEDIO… Hay dos buenas razones:

1. Podría crear una tabla que enumere las opciones de SUBTOTALES (1,2,3,4, etc.) y copiar una única fórmula hacia abajo para crear datos de resumen. (Esto podría ser un ahorro de tiempo especialmente grande si usted está tratando de calcular el SUBTOTALES SI como lo demostramos en este artículo).
2. La función SUBTOTALES puede utilizarse para calcular sólo las filas visibles (filtradas).

Nos centraremos en la segunda implementación de la Función SUBTOTALES.

En este ejemplo, utilizaremos la función para contar(CONTAR) las filas visibles estableciendo el argumento de la función SUBTOTALES_num a 3 (Una lista completa de posibles funciones se puede encontrar aquí)

=SUBTOTALES(3;$D$2:$D$14)

 

Observe cómo cambian los resultados a medida que filtramos manualmente las filas.

SUBTOTALES SI

Para crear un «Subtotales Si», utilizaremos una combinación de SUMAPRODUCTO, SUBTOTALES, DESREF, FILA y MIN en una fórmula de matriz. Usando esta combinación, podemos esencialmente crear una función genérica «SUBTOTALES SI». Veamos un ejemplo.

Tenemos una lista de miembros y su estado de asistencia para cada evento:

Supongamos que se nos pide que contemos el número de miembros que han asistido a un evento de forma dinámica ya que filtramos manualmente la lista así:

Para lograrlo, podemos utilizar esta fórmula:

=SUMAPRODUCTO((<rango de valores>=<criterio>)*(SUBTOTALES(3;DESREF(<primera celda del rango>;FILA(<rango de valores>)-MIN(FILA(<rango de valores>));0))))

=SUMAPRODUCTO((D2:D14="Attended")*(SUBTOTALES(3;DESREF(D2;FILA(D2:D14)-MIN(FILA(D2:D14));0))))

Cuando se utiliza Excel 2019 y versiones anteriores, debe introducir la fórmula de la matriz presionando CTRL + SHIFT + ENTER para decirle a Excel que está introduciendo una fórmula de matriz. Sabrás que la fórmula se introdujo correctamente como una fórmula de matriz cuando aparezcan corchetes alrededor de la fórmula (ver imagen anterior).

¿Cómo funciona la fórmula? La fórmula funciona multiplicando dos matrices dentro de SUMAPRODUCTO, donde la primera matriz se ocupa de nuestros criterios y la segunda matriz filtra sólo las filas visibles:

=SUMAPRODUCTO(<array de criterios>*<array de visibilidad>)

La Matriz de Criterios

El array de criterios evalúa cada fila de nuestro rango de valores (Estado «Attended» en este ejemplo) y genera un array como este

=(<rango de valores>=<criterios>)

=(D2:D14="Attended")

Salida:

{VERDADERO; FALSO; FALSO; VERDADERO; FALSO; VERDADERO; VERDADERO; FALSO; FALSO; VERDADERO; VERDADERO}

Tenga en cuenta que la salida en el primer array de nuestra fórmula ignora si la fila es visible o no, que es donde nuestro segundo array viene a ayudar.

 

La Matriz de Visibilidad

Utilizando SUBTOTALES para excluir las filas no visibles en nuestro rango, podemos generar nuestra matriz de visibilidad. Sin embargo, el SUBTOTALES por sí solo devolverá un único valor, mientras que SUMAPRODUCTO espera una matriz de valores. Para evitar esto, utilizamos DESREF para pasar una fila cada vez. Esta técnica requiere alimentar a DESREF con un array que contenga un número cada vez. El segundo array tiene el siguiente aspecto

=SUBTOTALES(3,DESREF(<primera celda del rango>;FILA(<rango de valores>)-MIN(FILA(<rango de valores>));0))

=SUBTOTALES(3;DESREF(D2;FILA(D2:D14)-MIN(FILA(D2:D14));0))

Salida:

{1;1;0;0;1;1}

Uniendo los Dos:

=SUMAPRODUCTO({VERDADERO; VERDADERO; FALSO; FALSO; VERDADERO; VERDADERO} * {1; 1; 0; 0; 1; 1})

= 4

SUBTOTALES SI con Múltiples Criterios

Para añadir múltiples criterios, simplemente multiplique más criterios dentro del SUMAPRODUCTO así

=SUMAPRODUCTO((<rango de valores 1>=<criterio 1>)*(<rango de valores 2>=<criterio 2>)*(SUBTOTALES(3;DESREF(<primera celda del rango>;FILA(<rango de valores>)-MIN(FILA(<rango de valores>));0))))

=SUMAPRODUCTO((E2:E14="Attended")*(B2:B14=2019)*(SUBTOTALES(3;DESREF(E2;FILA(E2:E14)-MIN(FILA(E2:E14));0))))

 

SUBTOTALES SI en Google Sheets

La función SUBTOTALES SI funciona exactamente igual en Google Sheets que en Excel. Excepto que observa que cuando utiliza CTRL + SHIFT + ENTER para introducir la fórmula de la matriz, Google Sheets añade la función ARRAYFORMULA a la fórmula (también puede añadir esta función manualmente).