Fonction SOMMEPROD

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 )

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).

Type	Syntaxe	Signification
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	“>”&2000	Supérieur à 2000
	“>=”&2000	Supérieur ou égal à 2000
	“<”&2000	Inférieur à 2000
	“<=”&2000	Inférieur ou égal à 2000
	2000	Montant 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.