Descarga el libro de trabajo de ejemplo
Este tutorial demostrará cómo calcular “sumproducto si”, devolviendo la suma de los productos de matrices o rangos basados en criterios.
Función SUMPRODUCT
La función SUMPRODUCT se utiliza para multiplicar matrices de números, sumando la matriz resultante.
Para crear un “Sumproducto Si”, usaremos la Función SUMPRODUCTO junto con la Función SI en una fórmula de matriz.
SUMPRODUCT SI
Combinando SUMPRODUCT e IF en una fórmula de matriz, básicamente podemos crear una función "SUMPRODUCT IF" que funciona de manera similar a cómo funciona la función incorporada SUMIF. Veamos un ejemplo.
Tenemos una lista de ventas logradas por gerentes en diferentes regiones con las tasas de comisión correspondientes:
Supongamos que se nos pide que calculemos el monto de la comisión para cada gerente de la siguiente manera:
Para lograr esto, podemos anidar una función SI con el gerente como nuestros criterios dentro de la función SUMPRODUCT así:
= SUMPRODUCTO (SI (=, *))
= SUMPRODUCTO (SI ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10))
Cuando use Excel 2022 y versiones anteriores, debe ingresar la fórmula presionando CTRL + MAYÚS + ENTRAR para obtener las llaves alrededor de la fórmula (ver imagen superior).
¿Cómo funciona la fórmula?
La fórmula funciona evaluando cada celda en nuestro rango de criterios como VERDADERO o FALSO.
Calculando la comisión total para Olivia:
= SUMPRODUCTO (SI ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10))
= SUMPRODUCTO (SI ({VERDADERO; VERDADERO; FALSO; FALSO; FALSO; VERDADERO; FALSO; FALSO; FALSO}, {928.62; 668.22; 919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61}))
A continuación, la función SI reemplaza cada valor con FALSO si no se cumple su condición.
= SUMPRODUCTO ({928.62; 668.22; FALSO; FALSO; FALSO; 480.564; FALSO; FALSO; FALSO})
Ahora la función SUMPRODUCT omite los valores FALSE y suma los valores restantes (2077,40).
SUMPRODUCT IF con múltiples criterios
Para usar SUMPRODUCT IF con múltiples criterios (similar a cómo funciona la función SUMIFS incorporada), simplemente anide más funciones IF en la función SUMPRODUCT de la siguiente manera:
= SUMPRODUCTO (SI (=, SI (=, *))
(CTRL + MAYÚS + ENTRAR)
= SUMPRODUCTO (SI ($ B $ 2: $ B $ 10 = $ G2, SI ($ C $ 2: $ C $ 10 = $ H2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10)))
(CTRL + MAYÚS + ENTRAR)
Otro enfoque de SUMPRODUCT IF
A menudo, en Excel, hay varias formas de obtener los resultados deseados. Una forma diferente de calcular "sumproducto si" es incluir los criterios dentro de la función SUMPRODUCT como una matriz usando doble unario así:
= SUMPRODUCTO (- ($ B $ 2: $ B $ 10 = $ G2), - ($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10)
Este método usa el unario doble (-) para convertir una matriz VERDADERO FALSO en ceros y unos. SUMPRODUCT luego multiplica las matrices de criterios convertidas juntas:
= SUMPRODUCTO ({1; 1; 0; 0; 0; 1; 0; 0; 0}, {1; 0; 1; 0; 1; 0; 0; 0; 0}, {928.62; 668.22; 919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61})
Consejos y trucos:
- Siempre que sea posible, siempre bloquee la referencia (F4) de sus rangos y entradas de fórmulas para permitir el llenado automático.
- Si está utilizando Excel 2022 o una versión más reciente, puede ingresar la fórmula sin Ctrl + Shift + Enter.
SUMPRODUCT SI en Hojas de cálculo de Google
La función SUMPRODUCT IF funciona exactamente igual en Google Sheets que en Excel:
