Fonction Excel : SOMMEPROD
La fonction SOMMEPROD renvoie la somme de plages de valeurs multipliées entre elles, ligne par ligne.
Utilisation :
=SOMMEPROD(plage_1)
ou
=SOMMEPROD(plage_1; plage_2)
ou
=SOMMEPROD(plage_1; plage_2; plage_3; etc)
Exemple d'utilisation de SOMMEPROD
Pour mieux comprendre le fonctionnement de SOMMEPROD et à quoi correspond sa définition de "somme des produits", voici un exemple simple pour commencer :
Dans ce premier tableau, les coûts totaux à la colonne D sont obtenus en multipliant les valeurs des colonnes B et C à l'aide de la formule =B2*C2 et le total est simplement la somme de ces différents produits =SOMME(D2:D11).
L'objectif maintenant est de calculer la somme de ces différents produits directement (sans colonne "Coût total") :
Pour faire cela, insérez la fonction SOMMEPROD puis ajoutez les 2 plages de cellules contenant les données à multiplier entre-elles (ligne par ligne) et dont vous calculer la somme :
La formule =SOMMEPROD(B2:B11;C2:C11) retourne bien ici le même total que dans le premier exemple :
Notez que le même résultat peut être obtenu avec la formule =SOMMEPROD((B2:B11)*(C2:C11)) :
Dans ce cas, le produit des 2 plages (B2:B11)*(C2:C11) est calculé et retourné sous la forme d'une plage unique dont la fonction SOMMEPROD renvoie la somme.
SOMMEPROD avec une condition
Pour effectuer le même calcul mais uniquement pour les articles dont la quantité est supérieure à 100, copiez pour commencer la dernière formule et ajoutez-y la condition >100.
La formule devient alors =SOMMEPROD((B2:B11>100)*(C2:C11)) :
Cette formule ne renvoie pas le résultat attendu car (B2:B11>100) ne contient aucune quantité, c'est au contraire une plage qui retourne 1 (VRAI) ou 0 (FAUX) en fonction du test >100.
C'est l'équivalent de la précédente fonction SOMMEPROD (sans condition) avec ces valeurs :
Dans ce cas, il faut donc ajouter la condition en plus des 2 autres plages :
=SOMMEPROD((B2:B11)*(C2:C11)*(B2:B11>100))
Ici, la quantité est multipliée par le coût par unité puis par 0 ou 1 en fonction du test (les produits qui ne doivent pas être pris en compte seront multipliés par 0 et ne modifieront donc pas la somme).
Cette fois-ci, la fonction SOMMEPROD a bien retourné la somme des produits en tenant compte de la condition :
SOMMEPROD avec plusieurs conditions
Pour simplifier un peu la formule (avant de la complexifier), la fonction SOMMEPROD devra retourner ici la somme des points des joueurs avec un résultat >100.
La formule est alors =SOMMEPROD((C2:C11)*(B2:B11>100)) :
Pour faire maintenant la somme des points dont le résultat est >100 ou <20, la formule devient :
=SOMMEPROD((C2:C11)*((B2:B11>100)+(B2:B11<20)))
Pour mieux comprendre ((B2:B11>100)+(B2:B11<20)), rappelez-vous qu'un test retourne 0 ou 1.
Dans ce cas, les plages sont additionnées pour obtenir la plage ((B2:B11>100)+(B2:B11<20)) qui est le résultat de l'addition des 0 et des 1 des 2 plages :
Si l'on inverse maintenant les conditions pour faire la somme des points dont le résultat est <=100 et >20, il ne suffira pas d'inverser les tests.
La formule =SOMMEPROD((C2:C11)*((B2:B11<=100)+(B2:B11>=20))) ne renverra pas le résultat attendu :
Pour que cela fonctionne, il faut ajouter une condition à la plage ((B2:B11<=100)+(B2:B11>=20)) pour conserver uniquement ceux qui ont validé les 2 tests (donc ceux =2).
La formule devient donc =SOMMEPROD((C2:C11)*(((B2:B11<=100)+(B2:B11>=20))=2)) :
