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 + A3xB3Cependant, 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 videsCe 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 conditionsDé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.
Formules en lien
- SOMME.SI plusieurs critères
- SOMME de RECHERCHEV
- RECHERCHEV avec plusieurs critères
- NB.SI avec plusieurs critères
- Compter nombre de valeurs texte différentes
- Calculer la moyenne pondérée
