Qu'est-ce que la fonction SOMMEPROD ?
SOMMEPROD (SUMPRODUCT en anglais) te sert dès que tu dois croiser plusieurs colonnes dans un même calcul, sans créer de colonne intermédiaire ni empiler des formules matricielles compliquées. C'est la fonction vers laquelle te tourner quand une condition doit se combiner avec une multiplication dans le même calcul.
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
=SOMMEPROD(matrice1; [matrice2]; [matrice3]; ...)Clique sur un argument pour aller à son explication.
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.
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.
Exemples pratiques pas à pas
Comment calculer une somme de produits avec la fonction SOMMEPROD
On te réclame le chiffre d'affaires du mois avant la réunion de l'après-midi, et personne n'a pris le temps de préparer la moindre colonne de calcul. Le tableau liste 18 produits avec leur catégorie, la quantité vendue en colonne C et le prix unitaire en colonne D, et tu veux le total encaissé sans polluer la feuille avec une colonne intermédiaire. La fonction SOMMEPROD multiplie chaque quantité par son prix ligne par ligne, additionne tous ces produits et te sort le montant dans une seule cellule.
- 1Dans une cellule, écris
=SOMMEPROD(. - 2En 1er argument : sélectionne la plage des quantités,
C2:C19. Chacune de ses valeurs sert de premier facteur, ligne par ligne. - 3En 2ᵉ argument : sélectionne la plage des prix unitaires,
D2:D19. Elle doit couvrir exactement les mêmes 18 lignes queC2:C19, sinon la fonction SOMMEPROD ne sait plus quelle quantité apparier à quel prix. Elle multiplie alorsC2parD2,C3parD3, jusqu'au bas du tableau, puis additionne les 18 produits pour donner 50 005 €. - 4Ferme la parenthèse et appuie sur Entrée.
=SOMMEPROD(C2:C19;D2:D19)
Comment calculer une moyenne pondérée avec les fonctions SOMMEPROD et SOMME
Les conseils de classe tombent la semaine prochaine et il faut sortir la moyenne de chaque élève, coefficients compris. Le tableau donne pour chaque matière la note obtenue en colonne B et son coefficient en colonne C, et la fonction MOYENNE ne convient pas ici puisqu'elle mettrait les quatre heures de maths sur le même plan que les deux heures d'histoire. La fonction SOMMEPROD sait pondérer chaque note par son coefficient, et il ne reste plus qu'à ramener le total au bon dénominateur.
- 1Dans une cellule, écris
=SOMMEPROD(. - 2En 1er argument : sélectionne la plage des notes,
B2:B6. Ce sont les valeurs que tu veux pondérer. - 3En 2ᵉ argument : sélectionne la plage des coefficients,
C2:C6, dans le même ordre et sur les mêmes lignes. La fonction SOMMEPROD multiplie chaque note par le coefficient qui lui fait face et additionne le tout, ce qui donne 190. - 4Ferme la parenthèse, complète par
/SOMME(C2:C6)pour diviser par le cumul des coefficients (14), puis appuie sur Entrée. C'est cette division qui transforme une somme pondérée en véritable moyenne, ici 13,57.
=SOMMEPROD(B2:B6;C2:C6)/SOMME(C2:C6)
Comment calculer un total sous condition avec la fonction SOMMEPROD
Ton associé veut savoir avant ce soir ce que la gamme « Info » a rapporté ce mois-ci, et tu n'as ni envie de filtrer le tableau ni de sommer les lignes à la main. Chaque ligne porte sa catégorie en colonne B, sa quantité en C et son prix unitaire en D, et tu veux le total d'une seule catégorie sans toucher aux données. La fonction SOMMEPROD gère ça toute seule : une condition glissée entre parenthèses éteint les lignes qui ne t'intéressent pas avant même la multiplication.
- 1Dans une cellule, écris
=SOMMEPROD(. - 2En 1er argument : écris d'un bloc le calcul de chaque ligne,
(B2:B7="Info")*C2:C7*D2:D7. La condition entre parenthèses fabrique un tableau de VRAI et de FAUX que la fonction SOMMEPROD lit comme des 1 et des 0. En multipliant ce tableau par la quantitéC2:C7puis par le prixD2:D7, les lignes hors catégorie passent par un 0 et s'éteignent, tandis que les trois lignes « Info » gardent leur produit quantité × prix. - 3Ferme la parenthèse et appuie sur Entrée. La fonction SOMMEPROD additionne les six produits, dont trois sont nuls, et affiche 14 400 €.
=SOMMEPROD((B2:B7="Info")*C2:C7*D2:D7)
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
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.
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.
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é.
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.
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
Continue sur ta lancée après la fonction SOMMEPROD : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
