Descarga el libro de trabajo de ejemplo
Este tutorial demostrará cómo calcular el "subtotal si", contando solo las filas visibles con criterios.
Función SUBTOTAL
La función SUBTOTAL puede realizar varios cálculos en un rango de datos (recuento, suma, promedio, etc.). Lo más importante es que se puede usar para calcular solo en filas visibles (filtradas). En este ejemplo, usaremos la función para contar (CONTAR) filas visibles estableciendo el argumento SUBTOTAL núm_función en 3 (aquí se puede encontrar una lista completa de posibles funciones).
= SUBTOTAL (3, $ D $ 2: $ D $ 14)
Observe cómo cambian los resultados a medida que filtramos filas manualmente.
SUBTOTAL SI
Para crear un "Subtotal If", usaremos una combinación de SUMPRODUCT, SUBTOTAL, OFFSET, ROW y MIN en una fórmula de matriz. Usando esta combinación, básicamente podemos crear una función genérica “SUBTOTAL 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 de la siguiente manera:
Para lograr esto, podemos usar esta fórmula:
= SUMPRODUCTO ((=) * (SUBTOTAL (3, DESPLAZAMIENTO (, FILA () - MIN (FILA ()), 0))))
= SUMPRODUCTO ((D2: D14 = "Atendido") * (SUBTOTAL (3, DESPLAZAMIENTO (D2, FILA (D2: D14) -MIN (FILA (D2: D14)), 0))))
Cuando use Excel 2022 y versiones anteriores, debe ingresar la fórmula de matriz presionando CTRL + MAYÚS + ENTRAR para decirle a Excel que está ingresando una fórmula de matriz. Sabrá que la fórmula se ingresó correctamente como una fórmula de matriz cuando aparezcan llaves alrededor de la fórmula (vea la imagen de arriba).
¿Cómo funciona la fórmula?
La fórmula funciona multiplicando dos matrices dentro de SUMPRODUCT, donde la primera matriz se ocupa de nuestros criterios y la segunda matriz filtra solo a filas visibles:
= SUMPRODUCTO (*)
La matriz de criterios
La matriz de criterios evalúa cada fila en nuestro rango de valores (estado "atendido" en este ejemplo) y genera una matriz como esta:
=(=)
= (D2: D14 = "Asistido")
Producción:
{CIERTO; FALSO; FALSO; CIERTO; FALSO; TURE; TURE; TURE; FALSO; FALSO; CIERTO; FALSO; CIERTO}
Tenga en cuenta que la salida en la primera matriz en nuestra fórmula ignora si la fila es visible o no, que es donde nuestra segunda matriz interviene para ayudar.
La matriz de visibilidad
Usando SUBTOTAL para excluir filas no visibles en nuestro rango, podemos generar nuestra matriz de visibilidad. Sin embargo, SUBTOTAL solo devolverá un solo valor, mientras que SUMPRODUCT espera una matriz de valores. Para solucionar esto, usamos OFFSET para pasar una fila a la vez. Esta técnica requiere alimentar a OFFSET una matriz que contiene un número a la vez. La segunda matriz se ve así:
= SUBTOTAL (3, DESPLAZAMIENTO (, FILA () - MIN (FILA ()), 0))
= SUBTOTAL (3, DESPLAZAMIENTO (D2, FILA (D2: D14) -MIN (FILA (D2: D14)), 0))
Producción:
{1;1;0;0;1;1}
Coser los dos juntos:
= SUMPRODUCTO ({VERDADERO; VERDADERO; FALSO; FALSO; VERDADERO; VERDADERO} * {1; 1; 0; 0; 1; 1})
= 4
SUBTOTAL SI con varios criterios
Para agregar varios criterios, simplemente varios criterios más juntos dentro del SUMPRODUCTO así:
= SUMPRODUCTO ((=) * (=) * (SUBTOTAL (3, DESPLAZAMIENTO (, FILA () - MIN (FILA ()), 0))))
= SUMPRODUCTO ((E2: E14 = "Atendido") * (B2: B14 = 2019) * (SUBTOTAL (3, DESPLAZAMIENTO (E2, FILA (E2: E14) -MIN (FILA (E2: E14)), 0)) ))
SUBTOTAL SI en Hojas de cálculo de Google
La función SUBTOTAL IF funciona exactamente igual en Google Sheets que en Excel:
