SOMMEPROD avec SI

Fonctions utilisées :
Utiliser SOMMEPROD avec SI sur Excel

Résumé

On peut utiliser la fonction SOMMEPROD associée à la fonction SI pour obtenir une somme conditionnelle. C’est-à-dire que l’on va additionner des éléments répondant à certains critères. Dans l’exemple ci-dessus, la formule en H3 est :

= SOMMEPROD( SI( B3:B10="Chaise" ; 1 ; 0 ) ; D3:D10 ; E3:E10 )

Cette formule permet de calculer la valeur du stock en multipliant les quantités (plage D3:D10) par les prix (plage E3:E10) uniquement sur les articles « Chaise » (plage B3:B10). Selon votre version d’Excel, vous devrez valider la formule en utilisant Ctrl+Shift+Entrée.

Formule Excel – SOMMEPROD avec SI

= SOMMEPROD( SI( test_logique ; 1 ; 0 ) ; valeurs )

Explications

Dans l’exemple, l’objectif est d’obtenir les valeurs des stocks selon certains critères. Le calcul de la valeur du stock est obtenu par la fonction SOMMEPROD qui se charge de la multiplication de la matrice des quantités par la matrice des prix. Pour ajouter des critères, il faut se servir de la fonction SI.

Fonction SOMMEPROD

La fonction SOMMEPROD permet de multiplier des plages de données entre elles.

= SOMMEPROD( A1:A3 ; B1:B3 )
--> Résultat : A1xB1 + A2xB2 + A3xB3

Cette solution évite de devoir créer une colonne qui multiplie pour chaque ligne le prix par la quantité et de devoir faire la somme de cette nouvelle colonne.

Dans notre exemple, la fonction SOMMEPROD permet de calculer la valeur totale du stock en multipliant la plage de données des prix par la plage de données des quantités.

Fonction SOMMEPROD + Fonction SI

Utiliser la fonction SI directement dans la fonction SOMMEPROD permet d’ajouter des conditions. Dans notre exemple, la formule en H3 est :

= SOMMEPROD( SI( B3:B10="Chaise" ; 1 ; 0 ) ; D3:D10 ; E3:E10 )

Cette formule oblige la fonction SOMMEPROD à multiplier trois plages de données : la plage des quantités, la plage des prix et une plage composée de 0 et 1 suivant si le test se révèle vrai ou faux. Cette dernière plage de données est générée par la fonction SI :

= SI( B3:B10="Chaise" ; 1 ; 0 )

Le résultat renvoyé sera une liste de valeurs composée de 0 et 1 suivant si le résultat pour chaque cellule est vrai ou faux.

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

La fonction SOMMEPROD se chargera de multiplier les trois plages de données.

=SOMMEPROD( {1;0;1;0;1;0;1;0} ;
            {433;221;434;442;306;424;249;354} ;
            {153;272;208;304;298;239;212;247} )

= 1*433*153 + 0*221*272 + ... + 0*354*247

Le résultat sera la somme des valeurs des stocks pour les chaises.

Fonction SOMMEPROD + fonction SI + plusieurs critères

En utilisant la solution vue précédemment, il est possible de mettre plusieurs plages de données résultant de l’utilisation de la fonction SI. Il peut s’agir de deux tests de type texte. La formule dans la cellule H4 est :

=SOMMEPROD(
            SI( B3:B10="Chaise" ; 1 ; 0 ) ;
            SI( C3:C10="Salon" ; 1 ; 0 ) ;
            D3:D10 ; 
            E3:E10 )

Mais on pourrait également mettre des critères sur des valeurs numériques ou des dates. La formule dans la cellule H5 est :

=SOMMEPROD(
            SI( B3:B10="Chaise" ; 1 ; 0 ) ;
            SI( E3:E10>280 ; 1 ; 0 ) ;
            D3:D10 ; 
            E3:E10 )

Vous pouvons ajouter autant de critères que vous le souhaitez.

Fonction SOMMEPROD + logique booléenne

Pour simplifier votre formule, vous pouvez remplacer la fonction SI par la syntaxe de la double négative. En effet, lorsque vous faites un test logique sur Excel, les valeurs renvoyées seront VRAI ou FAUX :

= B3:B10="Chaise"
--> Résultat: {VRAI;FAUX;VRAI;FAUX;VRAI;FAUX;VRAI;FAUX}

En plaçant le test logique entre parenthèses et en mettant deux signes négatifs devant, nous indiquons à Excel vouloir convertir les valeurs en 1 et 0.

= --(B3:B10="Chaise")
--> Résultat: {1;0;1;0;1;0;1;0}

On peut intégrer cette syntaxe à la place de la fonction SI dans la fonction SOMMEPROD.

= SOMMEPROD( --(B3:B10="Chaise") ; D3:D10 ; E3:E10 )

La formule est un peu plus concise et renvoie le même résultat.

Plusieurs critères et formule simplifiée

Revenons au cas où nous souhaitons appliquer plusieurs critères à notre calcul. Nous pouvons utiliser un principe proche de la double négative. En effet, lorsque nous multiplions deux matrices entre elles composées de VRAI et FAUX, Excel les convertira automatiquement en 0 et 1.

= (B3:B10="Chaise")*(C3:C10="Salon")
= ({VRAI;FAUX;VRAI;FAUX;VRAI;FAUX;VRAI;FAUX})*({VRAI;VRAI;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX})
--> Résultat: {1;0;0;0;0;0;0;0}

Avec cette solution, on simplifie encore la lecture de la fonction SOMMEPROD :

=SOMMEPROD(
            (B3:B10="Chaise")*(C3:C10="Salon") ;
            D3:D10 ; 
            E3:E10 )

Formules en lien

4.9/5 - (7 votes)

Vous avez apprécié cet article ?

Pour me soutenir, cliquez sur cette publicité. Pas besoin d’acheter, un clic suffit.

Merci. 🤩

Annonces

Modèles Excel

+

Livret 

7 méthodes pour gagner 2H par semaine sur Excel

C’est gratuit 🤩

Les formations

Formations Excel

Parce que chacun devrait avoir les moyens de faire son travail sur Excel

Découvrez des formations accessibles, rapides et directement applicables.