EXCEL: SUMAPRODUCTO con SI

Para filtrar los resultados de SUMAPRODUCTO con criterios específicos, puede aplicar expresiones lógicas simples directamente a las matrices en la función, en lugar de utilizar la función SI. En el ejemplo que se muestra, las fórmulas en H5: H7 son:

=SUMAPRODUCTO(--(color="red"),quantity,price)
=SUMAPRODUCTO(--(state="tx"),--(color="red"),quantity,price) 
=SUMAPRODUCTO(--(state="co"),--(color="blue"),quantity,price)

donde se definen los siguientes rangos con nombre:

state=B5:B14
color=C5:C14
quantity=D5:D14
price=E5:E14

Si prefiere evitar los rangos con nombre, use los rangos ingresados ​​arriba como referencias absolutas. Las expresiones lógicas en H6 y H7 se pueden combinar, como se explica a continuación.

Cómo funciona esta fórmula

Este ejemplo ilustra una de las fortalezas clave de la función SUMAPRODUCTO: la capacidad de filtrar datos con expresiones lógicas básicas en lugar de la función SI. Dentro de SUMAPRODUCTO, la primera matriz es una expresión lógica para filtrar en el color "rojo":

--(color="red")

Esto da como resultado una matriz o valores VERDADERO FALSO, que se convierten en unos y ceros con la operación doble negativa (-). El resultado es esta matriz:

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

Observe que la matriz contiene 10 valores, uno para cada fila. Un uno indica una fila donde el color es "rojo" y un cero indica una fila con cualquier otro color.

A continuación, tenemos dos matrices más: una para cantidad y otra para precio. Junto con estos resultados de la primera matriz, tenemos:

=SUMAPRODUCTO({1;0;1;0;0;0;1;0;0;0},quantity,price)

Expandiendo las matrices, tenemos:

=SUMAPRODUCTO({1;0;1;0;0;0;1;0;0;0},{10;6;14;9;11;10;8;9;11;10},{15;18;15;16;18;18;15;16;18;16})

El comportamiento principal de SUMAPRODUCTO es multiplicar, luego sumar matrices. Como estamos trabajando con tres matrices, podemos visualizar la operación como se muestra en la tabla a continuación, donde la columna de resultados es el resultado de multiplicar matriz1 * matriz2 * matriz3:

array1	array2	array3	result
1	10	15	150
0	6	18	0
1	14	15	210
0	9	16	0
0	11	18	0
0	10	18	0
1	8	15	120
0	9	16	0
0	11	18	0
0	10	16	0

Observe que array1 funciona como un filtro: los valores cero aquí "cero" valores en filas donde el color no es "rojo". Volviendo a poner los resultados en SUMAPRODUCTO, tenemos:

=SUMAPRODUCTO({150;0;210;0;0;0;120;0;0;0})

Lo que devuelve un resultado final de 480.

Agregar criterios adicionales

Puede ampliar los criterios agregando otra expresión lógica. Por ejemplo, para encontrar las ventas totales donde el color es "Rojo" y el estado es "TX", H6 contiene:

=SUMAPRODUCTO(--(state="tx"),--(color="red"),quantity,price)

Nota: SUMAPRODUCTO no distingue entre mayúsculas y minúsculas.

Simplificando con una sola matriz

Los profesionales de Excel a menudo simplificarán un poco la sintaxis dentro de SUMAPRODUCTO al multiplicar las matrices directamente dentro de la matriz1 de esta manera:

=SUMAPRODUCTO((state="tx")*(color="red")*quantity*price)

Esto funciona porque la operación matemática (multiplicación) coacciona automáticamente los valores VERDADERO y FALSO de las dos primeras expresiones en unos y ceros.