Fórmula genérica
=SUMPRODUCT(expression,range)
Resumen
Para filtrar los resultados de SUMPRODUCT con criterios específicos, puede aplicar expresiones lógicas simples directamente a 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:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(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 anteriormente como referencias absolutas. Las expresiones lógicas en H6 y H7 se pueden combinar, como se explica a continuación.
Explicación
Este ejemplo ilustra una de las fortalezas clave de la función SUMPRODUCT: la capacidad de filtrar datos con expresiones lógicas básicas en lugar de la función SI. Dentro de SUMPRODUCT, la primera matriz es una expresión lógica para filtrar por 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 la cantidad y otra para el precio. Junto con estos resultados de la primera matriz, tenemos:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Ampliando las matrices, tenemos:
=SUMPRODUCT((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 SUMPRODUCT es multiplicar y luego sumar matrices. Como estamos trabajando con tres matrices, podemos visualizar la operación como se muestra en la siguiente tabla, donde la columna de resultado es el resultado de multiplicar matriz1 * matriz2 * matriz3 :
| array1 | array2 | array3 | resultado |
|---|---|---|---|
| 1 | 10 | 15 | 150 |
| 0 | 6 | 18 | 0 |
| 1 | 14 | 15 | 210 |
| 0 | 9 | dieciséis | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 18 | 0 |
| 1 | 8 | 15 | 120 |
| 0 | 9 | dieciséis | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | dieciséis | 0 |
Observe que matriz1 funciona como un filtro: los valores cero aquí "ponen a cero" valores en filas donde el color no es "rojo". Volviendo a poner los resultados en SUMPRODUCT, tenemos:
=SUMPRODUCT((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:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Nota: SUMPRODUCT no distingue entre mayúsculas y minúsculas.
Simplificando con una sola matriz
Los profesionales de Excel a menudo simplifican un poco la sintaxis dentro de SUMPRODUCT al multiplicar matrices directamente dentro de array1 de esta manera:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Esto funciona porque la operación matemática (multiplicación) convierte automáticamente los valores VERDADERO y FALSO de las dos primeras expresiones en unos y ceros.
