Formule générique
=SUMPRODUCT(expression,range)
Sommaire
Pour filtrer les résultats de SUMPRODUCT avec des critères spécifiques, vous pouvez appliquer des expressions logiques simples directement aux tableaux de la fonction, au lieu d'utiliser la fonction IF. Dans l'exemple illustré, les formules de H5: H7 sont:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
où les plages nommées suivantes sont définies:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Si vous préférez éviter les plages nommées, utilisez les plages entrées ci-dessus comme références absolues. Les expressions logiques dans H6 et H7 peuvent être combinées, comme expliqué ci-dessous.
Explication
Cet exemple illustre l'un des principaux atouts de la fonction SUMPRODUCT: la capacité de filtrer les données avec des expressions logiques de base au lieu de la fonction IF. A l'intérieur de SUMPRODUCT, le premier tableau est une expression logique à filtrer sur la couleur "rouge":
--(color="red")
Il en résulte un tableau ou des valeurs TRUE FALSE, qui sont converties en uns et en zéros avec l'opération double négatif (-). Le résultat est ce tableau:
(1;0;1;0;0;0;1;0;0;0)
Notez que le tableau contient 10 valeurs, une pour chaque ligne. Un un indique une ligne où la couleur est "rouge" et un zéro indique une ligne avec n'importe quelle autre couleur.
Ensuite, nous avons deux autres tableaux: un pour la quantité et un pour le prix. Avec cela résulte du premier tableau, nous avons:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
En développant les tableaux, nous avons:
=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))
Le comportement de base de SUMPRODUCT est de multiplier, puis de sommer les tableaux. Puisque nous travaillons avec trois tableaux, nous pouvons visualiser l'opération comme indiqué dans le tableau ci-dessous, où la colonne de résultat est le résultat de la multiplication de array1 * array2 * array3 :
| tableau1 | tableau2 | tableau3 | résultat |
|---|---|---|---|
| 1 | dix | 15 | 150 |
| 0 | 6 | 18 | 0 |
| 1 | 14 | 15 | 210 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | dix | 18 | 0 |
| 1 | 8 | 15 | 120 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | dix | 16 | 0 |
Remarquez que array1 fonctionne comme un filtre - zéro valeurs ici "zéro sur" les valeurs dans les lignes où la couleur n'est pas "rouge". En remettant les résultats dans SUMPRODUCT, nous avons:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Ce qui renvoie un résultat final de 480.
Ajout de critères supplémentaires
Vous pouvez étendre les critères en ajoutant une autre expression logique. Par exemple, pour trouver les ventes totales où la couleur est "Rouge" et l'état est "TX", H6 contient:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Remarque: SUMPRODUCT n'est pas sensible à la casse.
Simplifier avec une seule baie
Les pros d'Excel simplifieront souvent un peu la syntaxe de SUMPRODUCT en multipliant les tableaux directement dans array1 comme ceci:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Cela fonctionne car l'opération mathématique (multiplication) contraint automatiquement les valeurs VRAI et FAUX des deux premières expressions à des uns et des zéros.
