La fonction SOMMEPROD() est l'une des fonctions les plus puissantes et polyvalentes d'Excel. Elle permet de multiplier des plages de cellules entre elles, puis d'additionner les résultats. Ce guide vous montre comment l'utiliser efficacement avec des exemples concrets.
Qu'est-ce que la fonction SOMMEPROD() ?
La fonction SOMMEPROD() multiplie les éléments correspondants de plusieurs plages de cellules, puis additionne tous les produits obtenus. Elle est particulièrement utile pour :
- Calculer des totaux pondérés (notes avec coefficients)
- Effectuer des calculs conditionnels sans SOMME.SI
- Créer des formules matricielles sans utiliser Ctrl+Maj+Entrée
- Analyser des données avec plusieurs critères
Syntaxe de la fonction
=SOMMEPROD(plage1; [plage2]; [plage3]; ...)
| Paramètre | Description |
|---|---|
| plage1 | La première plage de cellules (obligatoire) |
| [plage2] | Plage supplémentaire à multiplier avec la première |
| [plage3] | Autres plages optionnelles |
Important : Toutes les plages doivent avoir la même taille (même nombre de lignes et colonnes).
Exemple pratique : Calcul d'une moyenne pondérée
Imaginons un tableau de notes avec leurs coefficients :
| Matière (A) | Note (B) | Coefficient (C) |
|---|---|---|
| Maths | 15 | 4 |
| Français | 12 | 3 |
| Anglais | 14 | 2 |
| Histoire | 16 | 2 |
Pour calculer la moyenne pondérée, vous devez :
- Multiplier chaque note par son coefficient
- Diviser par la somme des coefficients
=SOMMEPROD(B2:B5; C2:C5) / SOMME(C2:C5)
Calcul détaillé :
- (15×4) + (12×3) + (14×2) + (16×2) = 60 + 36 + 28 + 32 = 156
- 156 ÷ 11 = 14,18
Résultat : La moyenne pondérée est de 14,18/20
SOMMEPROD avec des conditions
L'un des usages les plus puissants de SOMMEPROD est le calcul conditionnel. Contrairement à SOMME.SI, vous pouvez combiner plusieurs critères facilement.
Exemple : Total des ventes par région
| Région (A) | Produit (B) | Montant (C) |
|---|---|---|
| Nord | Chaises | 500€ |
| Sud | Tables | 800€ |
| Nord | Tables | 600€ |
| Sud | Chaises | 450€ |
| Nord | Chaises | 350€ |
Pour calculer le total des ventes de Chaises dans la région Nord :
=SOMMEPROD((A2:A6="Nord")*(B2:B6="Chaises")*(C2:C6))
Explication :
(A2:A6="Nord")renvoie {1;0;1;0;1} (VRAI=1, FAUX=0)(B2:B6="Chaises")renvoie {1;0;0;1;1}- La multiplication donne {1;0;0;0;1}
- On multiplie par les montants : 500 + 350 = 850€
5 Astuces pour maîtriser SOMMEPROD
1. Compter avec des conditions multiples
SOMMEPROD peut remplacer NB.SI.ENS pour compter des éléments :
=SOMMEPROD((A2:A10="Nord")*(B2:B10="Chaises")*1)
Le *1 force la conversion en nombres.
2. Éviter les erreurs avec des plages de tailles différentes
Si vos plages n'ont pas la même taille, Excel affichera l'erreur #VALEUR!. Vérifiez toujours que vos plages contiennent le même nombre de cellules.
3. Utiliser des opérateurs de comparaison
Vous pouvez utiliser >, <, >=, <=, <> dans vos conditions :
=SOMMEPROD((B2:B10>100)*(C2:C10))
Cette formule additionne les valeurs de C uniquement si les valeurs de B sont supérieures à 100.
4. Combiner avec d'autres fonctions
SOMMEPROD fonctionne bien avec MOIS, ANNEE, GAUCHE, etc. :
=SOMMEPROD((MOIS(A2:A10)=1)*(B2:B10))
Additionne les montants de janvier uniquement.
5. Alternative à RECHERCHEV avec plusieurs critères
=SOMMEPROD((A2:A10="Nord")*(B2:B10="Chaises")*(C2:C10))
Plus flexible que RECHERCHEV quand vous avez plusieurs conditions de recherche.
Cas pratique : Gestion de stock
Voici un tableau de stock avec les prix unitaires et quantités :
| Produit (A) | Prix unitaire (B) | Quantité (C) | En promo (D) |
|---|---|---|---|
| Stylos | 2€ | 150 | Oui |
| Cahiers | 5€ | 80 | Non |
| Classeurs | 8€ | 45 | Oui |
| Règles | 1€ | 200 | Non |
Valeur totale du stock
=SOMMEPROD(B2:B5; C2:C5)
Résultat : (2×150) + (5×80) + (8×45) + (1×200) = 1 260€
Valeur des articles en promotion
=SOMMEPROD((D2:D5="Oui")*(B2:B5)*(C2:C5))
Résultat : (2×150) + (8×45) = 660€
Différence entre SOMMEPROD et SOMME matricielle
Avant Excel 365, pour faire des calculs matriciels, il fallait valider avec Ctrl+Maj+Entrée. SOMMEPROD simplifie cela :
| Méthode | Formule | Validation |
|---|---|---|
| Formule matricielle | =SOMME(B2:B5*C2:C5) |
Ctrl+Maj+Entrée |
| SOMMEPROD | =SOMMEPROD(B2:B5;C2:C5) |
Entrée simple |
Avantage SOMMEPROD : Plus simple, compatible avec toutes les versions d'Excel.
Erreurs courantes et solutions
1. Erreur #VALEUR!
Cause : Plages de tailles différentes ou cellules contenant du texte.
Solution : Vérifiez que toutes les plages ont exactement la même dimension.
2. Résultat incorrect avec des conditions
Cause : Oubli de multiplier les conditions entre elles.
Mauvais :
=SOMMEPROD(A2:A10="Nord"; C2:C10)
Correct :
=SOMMEPROD((A2:A10="Nord")*(C2:C10))
3. Performance lente
Cause : SOMMEPROD sur de très grandes plages (100 000+ lignes).
Solution : Limitez la taille des plages ou utilisez SOMME.SI.ENS si vous n'avez qu'une condition.
Conclusion
La fonction SOMMEPROD() est un outil incontournable pour les calculs avancés dans Excel. Elle permet de combiner multiplication, addition et conditions en une seule formule élégante. Que ce soit pour des moyennes pondérées, des analyses multi-critères ou des calculs de stock, maîtriser SOMMEPROD vous fera gagner un temps précieux.
Astuce ExcelGenius : Génération de formules complexes comme SOMMEPROD, création de tableaux en un clic, analyse de données instantanée... Notre IA fait le travail pour vous. Décrivez simplement ce que vous voulez calculer et obtenez la formule parfaite. C'est GRATUIT → Essayer ExcelGenius maintenant
Articles connexes :
- Découvrez la fonction SOMME() pour les additions simples
- Apprenez SOMME.SI() pour les sommes conditionnelles
- Maîtrisez RECHERCHEV pour croiser vos données