Maîtrisez SOMMEPROD : de la multiplication simple au filtre multi-critères

SOMMEPROD est une fonction qui permet de réaliser des calculs en multipliant des valeurs de différentes plages et en les additionnant simultanément.

On calcule la somme des produits, d'où le nom SOMMEPROD.

Mais il existe une autre utilisation de la fonction SOMMEPROD, souvent méconnue, qui permet de faire des calculs conditionnels, des statistiques, ou des analyses multicritères comme le feraient les fonctions SOMME.SI.ENS ou NB.SI.ENS mais de manière encore plus flexible.

Découvrons ensemble tout ce que SOMMEPROD peut faire, et pourquoi elle mérite une place de choix dans votre boîte à outils Excel.

SOMMEPROD

Syntaxe :

= SOMMEPROD(plage1; [plage2]; [plage3]; …)

La fonction SOMMEPROD multiplie les éléments de plusieurs plages entre eux, puis additionne les résultats.

Autrement dit, elle fait la somme des produits.

Exemple simple

Vous avez un tableau avec le prix unitaire des produits (P.U HT) et la quantité vendue.

Vous voulez connaitre le CA total.

Formule :

Insérer la fonction SOMMEPROD : 

=SOMMEPROD(A2:A4;B2:B4)

Sélectionnez les valeurs de la colonne Prix unitaire puis les valeurs de la colonne Quantité, séparées par un point-virgule.

Résultat :

Excel multiplie chaque quantité par son prix et fait la somme de tous les montants.

Le calcul est : (19×126) + (69x24) + (57×31) + (119x214) + (97×19)= 9326

C’est la méthode la plus rapide pour calculer un total pondéré sans colonne intermédiaire.

En ajoutant une colonne pour calculer la multiplication sur chaque ligne et en faisant le total général, on retrouve bien le même résultat.

SOMMEPROD pour calculer une moyenne pondérée

Une moyenne pondérée est une moyenne où chaque valeur à un coefficient.

L'exemple le plus classique de moyenne pondérée est le résultat d'un examen.

Par exemple on affecte un coefficient 7 à la note obtenue en maths, 3 à l'anglais, 4 au français etc.

Curieusement, il n'existe pas dans Excel de fonction qui calcule directement la moyenne pondérée.

Mais SOMMEPROD permet de calculer une moyenne pondérée en la combinant avec la fonction SOMME.

Exemple 2 : Calcul de la moyenne pondérée

Le tableau ci-dessous montre les notes obtenues par un élève à un examen.

A chaque note est affecté un coefficient.

Pour calculer la moyenne pondérée, on va faire la somme des produits avec la fonction SOMMEPROD, puis on divisera le résultat par la somme des coefficients avec la fonction SOMME.

Formule :

=SOMMEPROD(B6:B12;C6:C12)/SOMME(C6:C12)

Résultat : 13,79

Bien sûr il existe beaucoup d'autres exemples où l'on doit calculer la moyenne pondérée, par exemple le CUMP (Cout Unitaire Moyen Pondéré) d'un stock.

SOMMEPROD avec conditions

L’une des grandes forces de SOMMEPROD, souvent méconnue, c’est qu’elle peut intégrer des critères logiques (comme les fonctions NB.SI.ENS ou SOMME.SI.ENS), mais de manière plus flexible et plus puissante.

Exemple : condition simple

Le tableau ci-dessous montre les ventes de 6 vendeurs sur 4 régions.

On veut connaitre le CA par région.

Nous allons créer un second tableau pour insérer les fonctions SOMMEPROD.

On commencera avec la région "Nord" qui sera notre premier filtre.

Formule :

J'insère la fonction SOMMEPROD : =SOMMEPROD(

Je sélectionne la colonne des régions : (Tableau1[Région]

J'insère le symbole = et je mets le nom de la région entre guillemets :  ="Nord"

Je ferme la parenthèse puis je mets le symbole * , 

Enfin je sélectionne  la colonne CA : Tableau1[CA])

Note : On remarque qu'on utilise pas de point-virgule ici mais le symbole de multiplication *.

Résultat :

Explication :

Excel évalue la condition ("Nord"), renvoie 1 si vrai, 0 sinon,

puis multiplie par la valeur de la colonne CA ,

et additionne tous les résultats.

Ce fonctionnement matriciel avec un critère de filtre rend SOMMEPROD capable de remplacer SOMME.SI

Cela fonctionne aussi avec plusieurs critères, par exemple la région et le vendeur.

Dans ce cas SOMMEPROD à la même fonction que SOMME.SI.ENS.

Il suffit  d'ajouter le symbole * devant la parenthèse, de sélectionner la colonne correspondant à mon second critère , d'ajouter = et le critère entre guillemets .

Par exemple si je veux connaitre le CA de la vendeuse "Alice" dans la région "Nord", la fonction sera : 

C’est la même logique qu’un SOMME.SI.ENS, mais plus souple : on peut y ajouter des formules ou des fonctions à l’intérieur des critères.

Autres utilisations de SOMMEPROD

La force de SOMMEPROD est les nombreuses utilisations et combinaisons que l'on peut en faire : 

SOMMEPROD pour compter

Même si elle s’appelle SOMMEPROD, cette fonction peut aussi compter des valeurs comme NB.SI ou NB.SI.ENS.

SOMMEPROD pour comparer deux listes

Autre usage pratique : vérifier si deux colonnes contiennent les mêmes valeurs.

SOMMEPROD et logique booléenne

Les critères de SOMMEPROD fonctionnent aussi avec les opérateurs logiques :

• > ou < pour les comparaisons : Par exemple filtrer sur les ventes supérieures à 1000€
• * pour “ET” : Quand on veut faire la somme des produits selon un critère, ce que l'on a vu dans les exemples précédents
• + pour “OU” : Si on veut l'un ou l'autre des critères, on remplace le symbole * par +, par exemple les ventes de la région "Nord" ou "Sud".

SOMMEPROD et combinaisons avec d'autres fonctions

SOMMEPROD peut être encore plus puissante combinée avec d'autres fonctions.

SOMMEPROD peut être combinée avec :

• MOYENNE.SI pour calculer une moyenne pondérée sur un critère, exemple le coût moyen pondéré d'un article en stock,
• GAUCHE, DROITE, TEXTE.AVANT, CHERCHE … pour filtrer selon des conditions textuelles,
• MOIS, ANNEE, DATE pour filtrer sur des critères de temps, par exemple le CA total sur l'année 2025
• ARRONDI, ARRONDI.INF...
• SI, SI.CONDITIONS, SI.MULTIPLE pour des analyses précises basées sur des conditions.
• Et beaucoup d'autres fonctions...

 Tout ceci est vu en détails dans la fonction : Utilisation avancée des fonctions Excel.

Pourquoi SOMMEPROD est si puissante ? 

• Elle combine plusieurs critères librement sans limites de syntaxe.
• Elle évite d’utiliser des colonnes intermédiaires.
• Elle peut remplacer plusieurs fonctions conditionnelles.
• Elle est compatible avec toutes les versions d’Excel.
• Elle peut être combinée avec un grand nombre de fonctions.

En anglais :

La fonction SOMMEPROD s’appelle SUMPRODUCT en anglais.

Conclusion :

La fonction SOMMEPROD permet bien plus que de simplement calculer le produit de 2 colonnes comme son nom l’indique.

On peut calculer en filtrant sur des critères, compter des valeurs etc

C’est une des rares fonctions à pouvoir “penser en matrice”, c’est-à-dire traiter plusieurs conditions et colonnes simultanément.

Voilà, cet article sur la fonction SOMMEPROD est terminé.

Si vous voulez vous entrainer et maitriser la fonction SOMMEPROD, abonnez-vous au blog si ce n’est pas encore fait, pour recevoir du contenu exclusif réservé aux membres et progresser sur Excel.

Il vous suffit de renseigner votre prénom et votre adresse mail dans le formulaire ci-dessous ou dans la pop-up qui s’affiche parfois.

À bientôt sur le blog Maîtrisez Excel.

Steeve