Téléchargez l'exemple de classeur
Ce didacticiel montrera comment calculer « sumproduct if », renvoyant la somme des produits de tableaux ou de plages en fonction de critères.
Fonction SOMMEPROD
La fonction SOMMEPROD est utilisée pour multiplier des tableaux de nombres, en additionnant le tableau résultant.
Pour créer un "Sumproduct If", nous utiliserons la fonction SUMPRODUCT avec la fonction SI dans une formule matricielle.
SOMME PRODUIT SI
En combinant SUMPRODUCT et IF dans une formule matricielle, nous pouvons essentiellement créer une fonction «SUMPRODUCT IF» qui fonctionne de la même manière que la fonction SUMIF intégrée. Parcourons un exemple.
Nous avons une liste des ventes réalisées par les managers dans différentes régions avec les taux de commission correspondants :
Supposons qu'on nous demande de calculer le montant de la commission pour chaque gestionnaire comme suit :
Pour ce faire, nous pouvons imbriquer une fonction SI avec le directeur comme nos critères à l'intérieur de la fonction SOMMEPROD comme ceci :
=SOMMEPROD(SI(=,*))
=SOMMEPROD(SI($C$2:$C$10=$G2,$D$2:$D$10*$E$2:$E$10))
Lorsque vous utilisez Excel 2022 et versions antérieures, vous devez entrer la formule en appuyant sur CTRL + MAJ + ENTRÉE pour obtenir les accolades autour de la formule (voir l'image du haut).
Comment fonctionne la formule ?
La formule fonctionne en évaluant chaque cellule de notre plage de critères comme VRAI ou FAUX.
Calcul de la commission totale pour Olivia :
=SOMMEPROD(SI($C$2:$C$10=$G2,$D$2:$D$10*$E$2:$E$10))
= SOMMEPROD (SI({VRAI; VRAI;FAUX; FAUX; FAUX; VRAI; FAUX; FAUX; FAUX}, {928.62; 668.22;919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61}))
Ensuite, la fonction SI remplace chaque valeur par FALSE si sa condition n'est pas remplie.
= SOMMEPROD({928.62; 668.22; FAUX; FAUX; FAUX; 480,564; FAUX; FAUX; FAUX})
Maintenant, la fonction SUMPRODUCT ignore les valeurs FALSE et additionne les valeurs restantes (2 077,40).
SUMPRODUCT IF avec plusieurs critères
Pour utiliser SUMPRODUCT IF avec plusieurs critères (similaire au fonctionnement de la fonction SUMIFS intégrée), imbriquez simplement plus de fonctions IF dans la fonction SUMPRODUCT comme suit :
=SOMMEPROD(SI(=, SI(=, *))
(CTRL + MAJ + ENTRÉE)
=SUMPRODUCT(SI($B$2:$B$10=$G2,IF($C$2:$C$10=$H2,$D$2:$D$10*$E$2:$E$10)))
(CTRL + MAJ + ENTRÉE)
Une autre approche de SUMPRODUCT IF
Souvent, dans Excel, il existe plusieurs façons d'obtenir les résultats souhaités. Une façon différente de calculer « somme produit si » consiste à inclure les critères dans la fonction SOMMEPROD en tant que tableau utilisant un double unaire comme ceci :
=SUMPRODUCT(--($B$2:$B$10=$G2),--($C$2:$C$10=$H2),$D$2:$D$10*$E$2:$E$10)
Cette méthode utilise le double unaire (-) pour convertir un tableau VRAI FAUX en zéros et en uns. SUMPRODUCT multiplie ensuite les tableaux de critères convertis ensemble :
=SOMMEPROD({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})
Trucs et astuces:
- Dans la mesure du possible, verrouillez toujours la référence (F4) de vos plages et entrées de formule pour permettre le remplissage automatique.
- Si vous utilisez Excel 2022 ou une version plus récente, vous pouvez saisir la formule sans Ctrl + Maj + Entrée.
SOMME PRODUIT SI dans Google Sheets
La fonction SUMPRODUCT IF fonctionne exactement de la même manière dans Google Sheets que dans Excel :
