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 :

excel fonction somme sommeprod

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") :

excel total tableau sommeprod

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 :

excel fonction sommeprod

La formule =SOMMEPROD(B2:B11;C2:C11) retourne bien ici le même total que dans le premier exemple :

excel fonction sommeprod somme total

Notez que le même résultat peut être obtenu avec la formule =SOMMEPROD((B2:B11)*(C2:C11)) :

excel fonction sommeprod multiplication

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)) :

excel sommeprod superieur 100

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 :

excel fonction sommeprod 0 1

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 :

excel fonction sommeprod 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)) :

excel sommeprod somme condition

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)))

excel sommeprod somme plusieurs conditions

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 :

excel fonction sommeprod 2 conditions png

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 :

excel fonction sommeprod detail fonctionnement png

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)) :

excel fonction sommeprod conditions et png

En cas de besoin, vous pouvez télécharger le fichier avec ces exemples : sommeprod.xlsx