Formule SOMMEPROD SI – Excel et Google Sheets

Ce tutoriel montre comment calculer une formule « sommeproduit/si », en retournant 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 et additionner le tableau résultant.

Pour créer une formule « SOMMEPROD SI », nous allons utiliser la fonction SOMMEPROD avec la fonction SI dans une formule de tableau.

SOMMEPROD SI

En combinant SOMMEPROD et SI dans une formule de tableau, nous pouvons essentiellement créer une formule « SOMMEPROD SI » qui fonctionne de la même manière que la fonction SOMMESI intégrée. Prenons un exemple.

Nous disposons d’une liste de ventes réalisées par des gestionnaires dans différentes régions, avec les taux de commission correspondants :

Supposons que l’on nous demande de calculer le montant de la commission gagnée pour chaque gestionnaire comme suit :

Pour ce faire, nous pouvons imbriquer une fonction SI avec le gestionnaire comme critère à l’intérieur de la fonction SOMMEPROD comme suit :

=SOMMEPROD(SI(<plage critère>=<critère>;<plage valeurs1>*<plage valeurs2>))

=SOMMEPROD(SI($C$2:$C$10=$G2;$D$2:$D$10*$E$2:$E$10))

Lorsque vous utilisez Excel 2019 et les 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 cette 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; 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 FAUX si sa condition n’est pas remplie.

= SOMMEPROD({928.62; 668.22; FAUX; FAUX; FAUX; 480.564; FAUX; FAUX; FAUX})

Maintenant, la fonction SOMMEPROD ignore les valeurs FAUX et additionne les valeurs restantes (2 077,40).

SOMMEPROD SI avec Plusieurs Critères

Pour utiliser la fonction SOMMEPROD SI avec plusieurs critères (de la même manière que la fonction SOMME.SI.ENS intégrée), il suffit d’imbriquer plusieurs fonctions SI dans la fonction SOMMEPROD comme suit :

=SOMMEPROD(SI(<plage critère1>=<critère1>; SI(<plage critère2>=<critère2>; <plage valeurs1>*<plage valeur2>))

=SOMMEPROD(SI($B$2:$B$10=$G2;SI($C$2:$C$10=$H2;$D$2:$D$10*$E$2:$E$10)))

(CTRL + MAJ + ENTRÉE)

Une Deuxième Approche pour Créer une Formule SOMMEPROD SI

Souvent, dans Excel, il existe plusieurs façons d’obtenir les résultats souhaités. Une autre façon de calculer une formule « SOMMEPROD SI » est d’inclure les critères dans la fonction SOMMEPROD sous forme de tableau en utilisant le double unaire comme suit :

=SOMMEPROD(--($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. SOMMEPROD 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})

Conseils et astuces :

• Dans la mesure du possible, verrouillez toujours (F4) vos plages et entrées de formules pour permettre le remplissage automatique.
• Si vous utilisez Excel 2019 ou une version plus récente, vous pouvez saisir la formule sans Ctrl + Maj + Entrée.

 

SOMMEPROD SI dans Google Sheets

La fonction SOMMEPROD SI fonctionne exactement de la même manière dans Google Sheets et dans Excel, sauf que vous devez utiliser la fonction ARRAYFORMULA au lieu de CTRL+MAJ+ENTRÉE pour créer la formule de tableau.