La fonction SUMPRODUCT dans Excel
SOMMEPROD (Anglais: SUMPRODUCT) multiplie les cellules correspondantes de deux plages ou plus, puis additionne tous les produits, en une seule formule et sans colonnes auxiliaires.
C’est aussi la méthode classique pour additionner ou compter avec plusieurs conditions, car elle peut multiplier des comparaisons comme =(A2:A5="Est"), où VRAI compte pour 1 et FAUX pour 0.
Syntaxe
=SUMPRODUCT(array1, [array2], [array3], …)
| Argument | Ce que cela signifie |
|---|---|
| array1 | La première plage, ou une expression qui donne un tableau de nombres. |
| array2, …facultatif | D’autres plages exactement de même taille. Leurs cellules sont multipliées par celles de array1. |
Exemples
1. Chiffre d’affaires à partir de la quantité et du prix
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Article | Qté | Prix | Chiffre d’affaires | |
| 2 | Stylo | 10 | 1.5 | 55 | |
| 3 | Carnet | 4 | 3 | ||
| 4 | Dossier | 6 | 2 | ||
| 5 | Agrafeuse | 2 | 8 |
10 × 1.5 + 4 × 3 + 6 × 2 + 2 × 8 = 55. Aucune colonne de totaux par ligne n’est nécessaire.
2. Moyenne pondérée
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Élément | Note | Coefficient | Final | |
| 2 | Devoirs | 90 | 2 | 79 | |
| 3 | Projet | 70 | 3 | ||
| 4 | Examen | 80 | 5 |
La somme des notes × coefficients est 790, divisée par le total des coefficients, soit 10. Une MOYENNE (Anglais: AVERAGE) classique donnerait 80.
3. Chiffre d’affaires d’une région
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Région | Unités | Prix | CA Est | ||
| 2 | Est | 10 | 2 | 35 | ||
| 3 | Ouest | 5 | 4 | |||
| 4 | Est | 3 | 5 | |||
| 5 | Ouest | 8 | 1 |
La comparaison donne 1 pour les lignes Est et 0 pour les autres, donc seuls les produits Est sont additionnés : 20 + 15.
4. Compter les lignes avec deux conditions
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Région | Unités | Prix | Ouest, plus de 5 | ||
| 2 | Est | 10 | 2 | 1 | ||
| 3 | Ouest | 5 | 4 | |||
| 4 | Est | 3 | 5 | |||
| 5 | Ouest | 8 | 1 |
Une ligne n’est comptée que si les deux comparaisons sont VRAI, car 1 × 1 = 1 et tout nombre × 0 = 0.
Erreurs courantes
Plages de tailles différentes.
=SOMMEPROD(B2:B5;C2:C4)renvoie #VALEUR!.Correction : Faites en sorte que chaque plage couvre le même nombre de lignes et de colonnes.
Texte dans une plage numérique. Lorsque les plages sont données comme arguments séparés, les cellules texte sont traitées comme 0 : une faute de frappe fait discrètement baisser le total.
Correction : Vérifiez les données en comparant NB (Anglais: COUNT) et NBVAL (Anglais: COUNTA), ou nettoyez d’abord le texte.
Utiliser * entre des plages en espérant une somme.*
=B2:B5*C2:C5seul renvoie une liste de produits.Correction : Placez la multiplication dans SOMMEPROD (Anglais: SUMPRODUCT) pour additionner les produits.
Questions fréquemment posées
Quand faut-il utiliser SUMIFS à la place ?
Lorsque chaque condition est une simple comparaison sur une colonne, SOMME.SI.ENS (Anglais: SUMIFS) est plus lisible. Utilisez SOMMEPROD (Anglais: SUMPRODUCT) lorsque vous devez d’abord multiplier des colonnes ou calculer à l’intérieur d’une condition.
SUMPRODUCT doit-il être saisi comme formule matricielle ?
Non. Elle gère les tableaux toute seule, dans toutes les versions d’Excel.
Fonctions associées
- SUMIFSSUMIFS additionne les valeurs qui remplissent plusieurs conditions à la fois, comme les commandes payées d'une région.
- COUNTIFSCOUNTIFS compte les lignes qui remplissent plusieurs conditions à la fois, comme une région et une plage de montants.
- SUMSUM additionne des nombres, des cellules et des plages entières en une seule formule.