Fonction SOMMEPROD

La fonction SOMMEPROD est une fonction Excel permettant de multiplier des plages de cellules entre elles. Cependant, sa grande force n’est pas là. Elle permet de créer des fonctions de somme et de comptage très puissantes et beaucoup plus souples que les fonctions SOMME.SI, SOMME.SI.ENS, NB.SI, NB.SI.ENS.
Utilisation de la fonction SOMMEPROD sur Excel

Syntaxe

La fonction SOMMEPROD s’écrit de la façon suivante :

= SOMMEPROD( matrice1; [matrice2] ; [matrice3] ; ... )

Cette fonction possède au minimum un seul argument.

  • matrice – Plage de cellules à multiplier.

La fonction SOMMEPROD a, à l’origine, le rôle de multiplier des plages de données entre elles.

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

Cependant, elle revêt un intérêt tout particulier pour votre travail sur Excel, avec la bonne syntaxe, elle permet de calculer :

  • Une somme avec une ou plusieurs conditions ;
  • Le nombre de cellules répondant à une ou plusieurs conditions.
= SOMMEPROD( --( A1:A3 = "" ) ; ( B1:B3 ) )
--> Résultat : Somme des valeurs en B1:B3 
    si les cellules en A1:A3 sont vides

À ce titre, elle vient concurrencer la fonction SOMME.SI, la fonction SOMME.SI.ENS, la fonction NB.SI et la fonction NB.SI.ENS.

La fonction SOMMEPROD pour multiplier des plages de cellules

Utiliser la fonction SOMMEPROD dans sa version initiale sera intéressant dans plusieurs situations. Ce sera le cas dès lors que vous voudrez multiplier des prix contenus dans une colonne par les quantités contenues dans une autre colonne. Vous pouvez vous affranchir de créer une colonne « total » ou, mieux encore, apporter un contrôle supplémentaire à votre tableau.

= SOMMEPROD( C3:A14 ; D3:D14 )
Utilisation de la fonction SOMMEPROD pour multiplier deux plages de cellules

Utiliser la fonction SOMMEPROD pour compter et additionner

Pour utiliser la fonction SOMMEPROD pour compter et additionner, il faudra utiliser une syntaxe particulière. Il s’agira de mettre entre parenthèses une plage de cellules et une condition grâce à un opérateur logique (<, >, <>, <=, >=, =).

=(B3:B14 <> "")
--> On vérifie les cellules non vides

Ce type d’opération va renvoyer dans Excel une plage de données de même taille où il sera indiqué le résultat sous forme de VRAI / FAUX.

En mettant devant la formule un double négatif (appelé double unaire), Excel convertit les valeurs VRAI en 1 et les valeurs FAUX en 0.

= --(B3:B14 <> "")

En combinant le résultat avec la fonction SOMMEPROD, on obtient le nombre de cellules non vides.

= SOMMEPROD(--(B3:B14 <> ""))

Pour obtenir la somme d’une plage de cellules suivant des critères définis sur une autre plage, il suffit d’utiliser les deux notions vues précédemment, nous allons générer une plage de données composées de 0 (condition non remplie) et de 1 (condition remplie) que nous multiplierons par les valeurs à additionner.

= SOMMEPROD( --(B3:B14 <> "") ; D3:D14 )
--> une condition

= SOMMEPROD( 
   --( B3:B14 <> "" ) ;
   --( C3:C14 > 160 ) ; 
   D3:D14 )
--> deux conditions

À noter que pour effectuer des sommes conditionnelles, il n’est pas nécessaire d’indiquer les doubles négatives. Par défaut, Excel convertira les valeurs VRAI et FAUX pour effectuer la multiplication. Il faudra utilisation une autre syntaxe. On multipliera les opérations logiques entre elles ainsi qu’à la plage à additionner. Inspirez-vous de l’exemple suivant :

= SOMMEPROD( (B3:B14 <> "") * (D3:D14) )
--> une condition

= SOMMEPROD( 
   ( B3:B14 <> "" ) *
   ( C3:C14 > 160 ) * 
   ( D3:D14 ) )
--> deux conditions

Définir les critères

La fonction SOMMEPROD permet l’utilisation des opérateurs logiques (<, >, <>, <=, >=). Pour cela, il faut appliquer une syntaxe particulière. Le tableau suivant résume les différents critères applicables suivant le format de la valeur (nombre, texte, date).

TypeSyntaxeSignification
Texte“Production”la valeur est égale à Production
“<>Production”la valeur est différente de Production
Date“>”&DATE(2022;1;1)Supérieur au 01/01/2022
“>=”&DATE(2022;1;1)Supérieur ou égal au 01/01/2022
“<”&DATE(2022;1;1)Inférieur au 01/01/2022
“<=”&DATE(2022;1;1)Inférieur ou égal au 01/01/2022
DATE(2022;1;1)Date exacte
Nombres“>”&2000Supérieur à 2000
“>=”&2000Supérieur ou égal à 2000
“<”&2000Inférieur à 2000
“<=”&2000Inférieur ou égal à 2000
2000Montant exact
Autres conditions“=”Cellule vide (pas de formule ni de valeur nulle)
“”Valeur nulle (peut être le résultat d’une formule)
“<>”Cellule non vide (inclus zéro)

Utiliser la fonction SOMMEPROD avec d’autres fonctions

La fonction SOMMEPROD permet de créer des formules matricielles. C’est-à-dire qu’elle peut aider les autres formules à générer une plage de données et à effectuer des opérations dessus.

Par exemple, si nous avons une plage de cellules contenant du texte et que nous voulons faire la somme de l’ensemble des caractères composant chaque cellule, nous pouvons combiner la fonction SOMMEPROD avec la fonction NBCAR.

= SOMMEPROD( NBCAR( B3:B14) )

Caractéristiques de la fonction SOMMEPROD

  • Elle multiplie des plages de cellules entre elles ;
  • Le texte est interprété comme une valeur nulle (0) ;
  • Les arguments doivent être de même dimension (même nombre de lignes) ;
  • Elle ne supporte pas les caractères génériques ;
  • La fonction SOMMEPROD requiert beaucoup de puissance de calcul dès lors qu’il s’agit de plage de données importantes.
4.8/5 - (18 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.