SOMMEPROD (SUMPRODUCT en anglais) est l'une des fonctions Excel les plus polyvalentes et puissantes pour les analyses avancées. Elle multiplie les éléments correspondants de plusieurs tableaux ligne par ligne, puis additionne tous les résultats. Si tu travailles avec des calculs de chiffre d'affaires, des moyennes pondérées ou des analyses multi-critères, cette fonction va devenir ton meilleur allié.
Concrètement, c'est elle qui calcule un chiffre d'affaires total en multipliant quantités et prix unitaires sans colonne intermédiaire, qui produit une moyenne pondérée de notes avec coefficients différents, qui compte les ventes d'une région précise dépassant un certain montant, ou qui additionne les bonus des commerciaux selon leur taux individuel. Des facturations automatiques aux analyses de rentabilité, SOMMEPROD remplace souvent plusieurs formules imbriquées par une seule ligne.
Syntaxe de la fonction SOMMEPROD
=SOMMEPROD(matrice1; [matrice2]; [matrice3]; ...)Comprendre chaque paramètre de la fonction SOMMEPROD
Tu donnes à SOMMEPROD deux colonnes côte à côte, et à l'écran une seule cellule te renvoie le total : elle a multiplié la 1re valeur de matrice1 par la 1re de matrice2, la 2e par la 2e, puis tout additionné d'un coup.
Seule matrice1 est obligatoire ; passée seule, elle se contente d'additionner ses valeurs comme SOMME. Les matrices que tu ajoutes ensuite (jusqu'à 254 de plus) doivent avoir exactement les mêmes dimensions, sinon l'appariement ligne par ligne n'a plus de sens et la formule cale.
matrice1
: c'est le premier tableau de valeurs à multiplierÇa peut être une plage de cellules comme A1:A10, un tableau de nombres, ou même une formule qui retourne un tableau. Si tu n'utilises qu'une seule matrice, SOMMEPROD additionne simplement toutes les valeurs (comme SOMME).
Tu peux aussi y placer une expression logique entre parenthèses : (A1:A10="Paris") produit un tableau de VRAI/FAUX, que SOMMEPROD traite comme 1 et 0 lors de la multiplication. C'est la base du mode conditionnel de SOMMEPROD.
Astuce : Le principe fondamental : si tu as deux colonnes A et B avec [2, 3, 4] et [10, 20, 30], SOMMEPROD fait (2×10) + (3×20) + (4×30) = 20 + 60 + 120 = 200. Simple mais redoutablement efficace pour remplacer une colonne de calcul intermédiaire.
matrice2, matrice3, ...
: les tableaux supplémentaires à multiplier avec le premier(facultatif)Chaque tableau doit avoir exactement les mêmes dimensions que matrice1 (même nombre de lignes et de colonnes). Excel multiplie ligne par ligne : la première valeur de matrice1 avec la première de matrice2, la deuxième avec la deuxième, etc.
Tu peux ajouter jusqu'à 254 matrices supplémentaires, mais en pratique, on en utilise rarement plus de 3 ou 4. L'important est que toutes aient la même structure pour que la multiplication fonctionne correctement.
Astuce : Pour utiliser SOMMEPROD avec des critères conditionnels, transforme tes conditions en tableaux de 0 et 1 en mettant des expressions logiques entre parenthèses. Par exemple, =SOMMEPROD((A:A="Paris")*(B:B>1000)) compte combien de lignes ont « Paris » en A ET une valeur supérieure à 1000 en B. Chaque parenthèse produit VRAI (1) ou FAUX (0), la multiplication ne laisse passer que les lignes où tout est vrai.
Pas envie d'écrire la formule SOMMEPROD à la main ?
Génère-la avec notre IA
Exemples pratiques pas à pas
Commercial : calculer le chiffre d'affaires total
Tu es commercial et tu dois calculer le chiffre d'affaires généré par tes ventes du mois. Tu as une liste de produits avec les quantités vendues et les prix unitaires. Plutôt que de créer une colonne de calcul intermédiaire pour chaque ligne, SOMMEPROD fait tout en une seule formule.
| A | B | C | |
|---|---|---|---|
| 1 | Produit | Quantité | Prix unitaire |
| 2 | Ordinateur portable | 12 | 850 € |
| 3 | Souris sans fil | 45 | 25 € |
| 4 | Clavier mécanique | 18 | 120 € |
| 5 | Écran 27 pouces | 8 | 350 € |
| 6 | Total CA : 16 190 € |
=SOMMEPROD(B1:B4; C1:C4)La fonction multiplie chaque quantité par le prix unitaire correspondant puis additionne : (12×850) + (45×25) + (18×120) + (8×350) = 16 190 €. Sans elle, tu aurais dû créer une colonne intermédiaire Quantité × Prix avant de la sommer ; ici tout tient en une formule qui se recalcule automatiquement.
Éducation : calculer une moyenne pondérée de notes
Tu travailles pour une école et tu dois calculer la moyenne pondérée d'un étudiant. Chaque matière a un coefficient différent. La simple fonction MOYENNE ne convient pas ici : il faut multiplier chaque note par son coefficient avant de calculer la moyenne.
| A | B | C | |
|---|---|---|---|
| 1 | Matière | Note | Coefficient |
| 2 | Mathématiques | 15 | 4 |
| 3 | Français | 12 | 3 |
| 4 | Histoire | 14 | 2 |
| 5 | Anglais | 16 | 3 |
| 6 | Moyenne : 14,17 |
=SOMMEPROD(B1:B4; C1:C4)/SOMME(C1:C4)La fonction multiplie chaque note par son coefficient et additionne le tout, puis le résultat est divisé par la somme des coefficients : (15×4 + 12×3 + 14×2 + 16×3) / (4+3+2+3) = 170 / 12 = 14,17.
Analyste : compter avec plusieurs critères simultanés
Tu es analyste de données et tu dois compter combien de ventes ont été réalisées dans la région « Nord » avec un montant supérieur à 5 000 €. NB.SI.ENS peut gérer ce cas, mais SOMMEPROD offre plus de flexibilité pour des critères complexes, notamment des comparaisons calculées dynamiquement.
| A | B | |
|---|---|---|
| 1 | Région | Montant |
| 2 | Nord | 3 200 € |
| 3 | Nord | 6 500 € |
| 4 | Sud | 7 100 € |
| 5 | Nord | 5 800 € |
| 6 | Est | 4 900 € |
| 7 | Nord | 2 100 € |
| 8 | Nb ventes Nord > 5000 : 2 |
=SOMMEPROD((A1:A6="Nord")*(B1:B6>5000))Chaque condition entre parenthèses crée un tableau de VRAI (1) ou FAUX (0). Leur multiplication donne 1 uniquement quand toutes les conditions sont vraies, puis la fonction additionne ces 1 et 0 pour obtenir le nombre de lignes qui respectent tous les critères : ici les lignes à 6 500 € et 5 800 € dans la région Nord, soit 2.
Contrôle de gestion : calculer des bonus sur objectifs
Tu es contrôleur de gestion et tu dois calculer les bonus des commerciaux. Le bonus est un pourcentage du CA réalisé, mais ce pourcentage varie selon les individus : chaque ligne a son propre taux. Tu dois donc multiplier le CA de chaque commercial par son taux spécifique, puis additionner le tout.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Commercial | CA réalisé | Objectif | Taux bonus |
| 2 | Sophie | 120 000 € | 100 000 € | 3% |
| 3 | Marc | 85 000 € | 100 000 € | 1% |
| 4 | Julie | 150 000 € | 120 000 € | 4% |
| 5 | Total bonus : 10 850 € |
=SOMMEPROD(B1:B3; D1:D3)La fonction multiplie le CA de chaque commercial par son taux de bonus respectif et additionne : (120 000×3%) + (85 000×1%) + (150 000×4%) = 3 600 + 850 + 6 000 = 10 850 €. C'est le montant total à provisionner pour les bonus.
Mets la fonction SOMMEPROD en pratique avec un exercice corrigé
M'entraîner
Les erreurs fréquentes avec la fonction SOMMEPROD
Quand SOMMEPROD affiche #VALEUR!, c'est qu'elle a reçu des plages de tailles différentes (A1:A10 face à B1:B5) et ne sait plus quelle valeur apparier à laquelle.
Les deux autres ratés ne crient même pas : une cellule vide au milieu compte pour 0 et tire ta moyenne pondérée vers le bas en silence, et une colonne entière comme A:A fait mouliner Excel sur un million de lignes pour trois cents données réelles, d'où ces calculs qui traînent.
Erreur #VALEUR! -- tableaux de tailles différentes
C'est l'erreur la plus fréquente avec SOMMEPROD. Si tu écris =SOMMEPROD(A1:A10; B1:B5), Excel affiche #VALEUR! car les deux plages n'ont pas la même taille (10 lignes contre 5 lignes). Excel ne sait pas quelles valeurs multiplier ensemble.
Solution : Vérifie que toutes tes plages ont exactement les mêmes dimensions : même nombre de lignes ET de colonnes. Par exemple, aligne sur =SOMMEPROD(A1:A10; B1:B10). Sélectionne une plage et regarde la zone Nom en haut à gauche de la barre de formule pour vérifier sa taille.
Résultat inattendu à cause de cellules vides au milieu des données
Les cellules vides sont traitées comme des 0 par SOMMEPROD. Si ta plage contient des cellules vides au milieu des données, elles ne génèrent pas d'erreur mais donnent 0 dans le calcul, ce qui peut fausser une moyenne pondérée (le dénominateur reste le même mais certains numérateurs valent 0).
Solution : Assure-toi que tes plages ne contiennent pas de cellules vides au milieu des données. Si c'est inévitable, ajoute une condition pour les exclure : =SOMMEPROD((A1:A10<>"")*A1:A10*B1:B10) écarte les lignes où A est vide.
Formule très lente sur de grandes plages
Quand tu utilises SOMMEPROD avec des colonnes entières (comme A:A) et plusieurs conditions, Excel peut mettre plusieurs secondes à calculer car il traite plus d'un million de lignes, même si tes données n'en occupent que quelques centaines.
Solution : Limite tes plages aux données réelles. Au lieu de =SOMMEPROD((A:A="Paris")*B:B), utilise =SOMMEPROD((A2:A1000="Paris")*B2:B1000). Si tes données croissent régulièrement, convertis ton tableau en tableau structuré (Ctrl+T) : les références de type Tableau1[Région] s'adaptent automatiquement.
Confusion entre SOMMEPROD et SOMME.SI.ENS
Beaucoup utilisent SOMMEPROD alors que SOMME.SI.ENS serait plus rapide et plus lisible (ou inversement). SOMME.SI.ENS additionne une plage selon des critères fixes, tandis que SOMMEPROD multiplie d'abord plusieurs colonnes puis additionne, ce qui le rend indispensable pour les calculs quantité × prix ou les conditions calculées dynamiquement.
Solution : Choisis SOMME.SI.ENS pour additionner sur des critères simples et fixes (région = « Nord » ET statut = « Validé »). Choisis SOMMEPROD quand tu dois multiplier des colonnes entre elles (quantité × prix × taux) ou quand les critères impliquent des calculs dynamiques comme >MOYENNE(B:B).
SOMMEPROD vs SOMME.SI.ENS vs PRODUIT vs SOMME
À l'écran, ces quatre fonctions rendent toutes un nombre unique, mais pas par le même chemin : SOMMEPROD multiplie tes colonnes entre elles avant d'additionner, là où SOMME.SI.ENS se contente d'additionner une plage selon des critères, PRODUIT multiplie sans jamais sommer et SOMME somme sans jamais multiplier.
Garde SOMMEPROD pour les calculs quantité × prix, les bonus à taux variable et les moyennes pondérées ; bascule sur SOMME.SI.ENS dès qu'il s'agit juste d'additionner avec des filtres fixes, plus rapide et plus lisible sur de gros volumes.
| Critère | SOMMEPROD | SOMME.SI.ENS | PRODUIT | SOMME |
|---|---|---|---|---|
| Opération principale | Multiplie puis additionne | Additionne selon critères | Multiplie uniquement | Additionne uniquement |
| Gère plusieurs tableaux | ✅ Oui (jusqu'à 255) | ❌ Non (1 plage à additionner) | ✅ Oui | ✅ Oui |
| Critères conditionnels | ✅ Très flexible | ✅ Jusqu'à 127 critères | ❌ Non | ❌ Non |
| Calcul de moyenne pondérée | ✅ Parfait pour ça | ❌ Pas adapté | ❌ Non | ❌ Non |
| Vitesse sur grandes données | ⭐⭐ Moyen | ⭐⭐⭐ Rapide | ⭐⭐⭐ Rapide | ⭐⭐⭐ Rapide |
| Niveau de difficulté | ⭐⭐ Intermédiaire | ⭐⭐ Intermédiaire | ⭐ Débutant | ⭐ Débutant |
| Cas d'usage typique | CA = quantité × prix, bonus, moyenne pondérée | Total ventes Paris en janvier | Factorielle, intérêts composés | Total simple d'une colonne |
Astuces avancées avec SOMMEPROD
Utilise la double négation -- pour sécuriser les booléens
Place -- devant une condition pour forcer la conversion VRAI→1 / FAUX→0 de façon explicite : =SOMMEPROD(--(A1:A10="Paris"); B1:B10). Sans --, la multiplication fonctionne en général, mais la double négation rend la formule plus robuste et lisible.
Sur certaines versions d'Excel, les booléens non convertis peuvent produire des résultats inattendus dans des formules imbriquées.
Calcule un chiffre d'affaires conditionnel
Combine multiplication de colonnes et conditions dans une seule formule : =SOMMEPROD((A2:A100="Validée")*B2:B100*C2:C100) calcule le CA (quantité × prix) uniquement pour les lignes dont le statut est « Validée ». Les autres lignes sont multipliées par 0 et ignorées.
Ajoute autant de conditions que nécessaire : chaque parenthèse supplémentaire affine le filtre.
Compte avec des critères dynamiques liés à la date
Pour compter les lignes où une date tombe dans les 30 derniers jours, utilise =SOMMEPROD((A2:A100="Commercial")*(B2:B100>=AUJOURDHUI()-30)). NB.SI.ENS ne gère pas les critères calculés dynamiquement : SOMMEPROD prend le relais.
Remplace 30 par n'importe quel intervalle pour adapter la fenêtre temporelle sans modifier la structure.
Questions fréquentes sur la fonction SOMMEPROD
Quelle est la différence entre SOMMEPROD et SOMME.SI.ENS ?
SOMMEPROD multiplie les éléments de plusieurs tableaux puis additionne, tandis que SOMME.SI.ENS additionne une seule plage selon des critères définis. SOMMEPROD est plus flexible pour les calculs complexes (quantité × prix, moyenne pondérée, conditions calculées dynamiquement).
Dès que tu veux simplement additionner une colonne avec un ou plusieurs filtres simples, SOMME.SI.ENS est plus rapide et plus lisible. Utilise SOMMEPROD quand tu dois multiplier des colonnes entre elles ou quand les critères impliquent des formules.
Comment SOMMEPROD gère-t-elle les valeurs texte ?
SOMMEPROD traite automatiquement les valeurs texte comme des 0 dans les multiplications. Cela ne génère pas d'erreur mais peut fausser le résultat si tu n'y prêtes pas attention.
Pour convertir des conditions booléennes (VRAI/FAUX) en nombres (1/0) de façon explicite, tu peux utiliser la double négation -- devant tes conditions : =SOMMEPROD(--(A1:A10>100); B1:B10) force la conversion VRAI→1 avant la multiplication.
Peut-on utiliser SOMMEPROD avec plus de deux tableaux ?
Oui ! Tu peux utiliser jusqu'à 255 tableaux différents. Par exemple, =SOMMEPROD(A1:A10; B1:B10; C1:C10) multipliera les trois colonnes ligne par ligne avant de tout additionner. C'est très utile pour des calculs à trois dimensions comme quantité × prix × taux de remise.
Dans la pratique, on dépasse rarement 3 ou 4 matrices. Si la formule devient trop complexe, envisage de la décomposer en plusieurs colonnes de calcul intermédiaires pour la lisibilité.
SOMMEPROD fonctionne-t-elle pour compter avec plusieurs critères ?
Absolument ! C'est l'un de ses usages les plus puissants. =SOMMEPROD((A:A="Paris")*(B:B>100)) compte combien de lignes ont « Paris » en colonne A ET une valeur supérieure à 100 en colonne B.
Chaque condition entre parenthèses produit un tableau de VRAI (1) ou FAUX (0). La multiplication ne conserve que les lignes où toutes les conditions sont vraies. C'est une alternative efficace à NB.SI.ENS, particulièrement quand les critères impliquent des calculs dynamiques.
Pourquoi mes plages doivent-elles avoir la même taille ?
SOMMEPROD multiplie les valeurs élément par élément : première ligne avec première ligne, deuxième avec deuxième, etc. Si les plages ont des tailles différentes, Excel ne sait pas quoi multiplier ensemble et retourne #VALEUR!.
Assure-toi toujours que tes plages ont exactement le même nombre de lignes et de colonnes. La zone Nom (haut gauche de la barre de formule) t'indique la taille de la sélection en cours.
Pour aller plus loin
Les fonctions similaires : SOMME.SI.ENS, SOMME, PRODUIT, PRODUITMAT, SOMME.CARRES
Bloqué sur une formule Excel ?
Pose ta question à notre assistant Excel IA, il te sort la bonne formule en quelques secondes.
Essayer l'assistant IAGratuit · 10 questions par mois