¿cómo utilizar subtotal con si en excel?

ESTA FÓRMULA ES PURA MAGIA.

Si desea escribir una fórmula SUBTOTAL en Excel con IF (condición), necesita usar varias funciones para hacerlo. Pero antes de hacer eso, comprendamos los datos que tenemos para este ejemplo.

En el ejemplo anterior, tiene tres columnas:

• Nombre
• losa de edad
• Género

Y cuando filtra una losa de la columna Edad Losa, muestra la cantidad de mujeres en la celda F1. Esto significa que tenemos una fórmula que muestra la cantidad de valores filtrados pero con una condición.

La fórmula tenemos:

 =SUMPRODUCT((C2:C41=E1)*(SUBTOTAL(3,OFFSET(C2,ROW(C2:C41)-MIN(ROW(C2:C41)),0))))

Comprender la fórmula SUBTOTAL SI

Esta fórmula utiliza cinco funciones: SUMAPRODUCTO, SUBTOTAL, COMPENSACIÓN, FILA y MAX. Por tanto, para entender esta fórmula, debemos dividirla en varias partes.

1.LÍNEA(C2:C41)-2.0)

Esta parte de la fórmula utiliza las funciones MIN y FILA.

• En la FILA, hemos hecho referencia a las columnas «Género» y devuelve una matriz de números de fila.
• Después de eso, MIN toma esa matriz o números de fila y devuelve el número mínimo de fila. Por eso tenemos 2 en esta parte de la fórmula.

2.LÍNEA (C2:C41)

En esta parte, solo tenemos la función FILA, que devuelve una matriz de números de fila.

3. DESPLAZAMIENTO(C2,LÍNEA(C2:C41)-MIN(LÍNEA(C2:C41)),0)

Ahora tenemos la función OFFSET. Le ayuda a crear una referencia a un rango utilizando una referencia de celda como punto de partida. En el argumento de referencia , nos referimos a la celda C2, la primera celda desde la que comienza nuestro rango de género.

En el argumento de las líneas tenemos esta parte de la fórmula discutida anteriormente en las dos primeras partes. Después de eso, en el argumento cols usamos 0. Con todo esto, OFFSET devuelve una matriz de todos los valores en la columna «Sexo».

4. SUBTOTAL(3,DESPLAZAMIENTO(C2,LÍNEA(C2:C41)-MIN(LÍNEA(C2:C41)),0))

Usamos la matriz devuelta por OFFSET en el SUBTOTAL. Y en function_num usamos 3, que le dice a SUBTOTAL que use la función CONTARA para el cálculo.

Cuando utiliza un filtro en la columna «Edad de losa», esta parte SUBTOTAL de la fórmula devuelve una matriz que contiene 0 y 1.

En esta tabla tenemos 1 para valores equivalentes al valor al que aplicamos el filtro. Vea el ejemplo a continuación:

5. (C2:C41=E1)

Esta parte de la fórmula devuelve una matriz al probar una condición. En esta condición, probamos si el valor del rango es «Mujer» y devuelve VERDADERO y FALSO en la matriz.

En esta tabla, tenemos VERDADERO para el valor «Femenino» y FALSO para los demás.

7. Última parte

Al final, tenemos dos tablas en SUMPRODUCT. Y también tenemos un operador de asterisco entre estas matrices.

Cuando multiplicamos las dos tablas entre sí, tenemos una sola tabla con 0 y 1. En esta tabla, uno (1) es para el valor «Mujer» en género y 21-30 para «Edad Losa».

Al final, SUMPRODUCT devuelve la suma usando esta matriz. Y esta suma es la cantidad de celdas con el valor «Mujer» en la columna de género cuando filtra por 21-30 en la columna «Rango de edad».