Fonction SUMPRODUCT d’Excel

Dans Excel, la fonction SOMMEPROD permet de multiplier entre elles deux colonnes ou plus — ou des matrices — puis d’additionner les produits obtenus. En réalité, SOMMEPROD est une fonction particulièrement polyvalente : à l’instar des fonctions NB.SI.ENS ou SOMME.SI.ENS, elle permet aussi de compter ou d’additionner des valeurs selon plusieurs critères. Cet article présente sa syntaxe ainsi que quelques exemples concrets d’utilisation.

• Exemple 1 : Utilisation de base de la fonction SOMMEPROD
• Exemple 2 : Additionner des cellules selon plusieurs critères avec la fonction SOMMEPROD
• Exemple 3 : Compter des cellules selon plusieurs critères avec la fonction SOMMEPROD
• Exemple 4 : Calculer la moyenne pondérée avec la fonction SOMMEPROD

 Syntaxe :

La syntaxe de la fonction SOMMEPROD dans Excel est la suivante :

 Arguments :

• array1: Obligatoire. Première matrice ou plage de cellules à multiplier, puis à additionner.
• array2: Facultatif. Deuxième matrice ou plage de cellules à multiplier, puis à additionner.

Remarques :

• 1. Si votre plage contient des valeurs non numériques, la fonction SOMMEPROD les considère comme des zéros.
• 2. Si les matrices ou plages n’ont pas le même nombre de lignes et de colonnes, la fonction SOMMEPROD renvoie une erreur #VALEUR !
• 3. Si des tests logiques sont présents dans les matrices, ils renvoient les valeurs VRAI et FAUX. Dans la plupart des cas, vous devez les convertir en 1 et 0 à l’aide de l’opérateur unaire double (--) .
• 4. La fonction SOMMEPROD prend en charge jusqu’à 255 arguments dans Excel 2007 et les versions ultérieures, mais seulement 30 dans les versions antérieures.
• 5. La fonction SOMMEPROD ne prend pas en charge les caractères génériques.

 Résultat :

Renvoie le résultat de la multiplication et de l’addition des matrices.

 Exemples :

Exemple 1 : Utilisation de base de la fonction SOMMEPROD

L’utilisation classique de SOMMEPROD consiste à multiplier deux colonnes entre elles, puis à additionner les résultats. Par exemple, avec les données illustrées ci-dessous, vous souhaitez multiplier chaque quantité commandée par son prix unitaire, puis faire la somme de tous ces produits pour obtenir le montant total.

Pour effectuer cette opération, veuillez appliquer la formule suivante :

Explication : Cette formule SOMMEPROD(B2:B7;C2:C7) = B2*C2 + B3*C3 + B4*C4 + B5*C5 + B6*C6 + B7*C7.

Ensuite, appuyez sur la touche Entrée : les cellules de la colonne B sont multipliées par les cellules correspondantes de la même ligne dans la colonne C, et les résultats sont additionnés. Voir la capture d’écran :

Exemple 2 : Additionner des cellules selon plusieurs critères avec la fonction SOMMEPROD

Outre la fonction SOMME.SI.ENS, qui permet d’additionner des valeurs de cellules selon plusieurs critères, la fonction SOMMEPROD offre elle aussi une solution rapide et simple à ce type de problème.

Cas 1 : Additionner des valeurs de cellules si une autre colonne contient un texte spécifique

Supposons que vous disposiez d’un tableau de données comme ci-dessous. Vous souhaitez calculer le prix total des produits « KTE » attribués à « David ». Comment utiliser la fonction SOMMEPROD dans Excel pour obtenir ce résultat ?

1. Saisissez ou copiez l’une des formules suivantes dans une cellule vide :

• Conseils: Dans la formule ci-dessus :
• A2:A12=«KTE» : représente la première plage de critères ainsi que la condition selon laquelle vous souhaitez effectuer la somme.
• B2:B12=«David» : représente la deuxième plage de critères et le critère selon lesquels vous souhaitez effectuer la somme.
• D2:D12 : correspond à la plage à additionner, dont vous souhaitez totaliser les valeurs des cellules.

2. Ensuite, appuyez sur la touche Entrée pour obtenir le résultat souhaité :

Cas 2 : Additionner des cellules selon plusieurs critères avec une logique OU

Pour additionner des cellules selon plusieurs critères combinés par une logique OU, utilisez le symbole plus (+) pour relier les différentes conditions au sein de la fonction SOMMEPROD.

Par exemple, je souhaite additionner le prix total des produits « KTE » et « KTO » situés dans la colonne A, comme illustré ci-dessous :

Appliquez la formule suivante dans une cellule vide :

Appuyez ensuite sur la touche Entrée : le prix total des produits « KTE » et « KTO » est alors calculé. Voir la capture d’écran :

Cas 3 : Additionner des cellules selon plusieurs critères combinant les logiques OU et ET

Dans certaines situations, vous devrez peut-être additionner des cellules en combinant à la fois les logiques OU et ET. La fonction SOMMEPROD permet de résoudre ce type de cas en toute simplicité.

L’astérisque (*) sert d’opérateur ET.

Le symbole plus (+) sert d’opérateur OU.

Prenons un exemple : additionner le prix total des produits « KTE » et « KTO » lorsque le montant de la vente dépasse 200.

Copiez ou saisissez la formule suivante :

Appuyez ensuite sur la touche Entréepour obtenir le résultat souhaité :

Exemple 3 : Compter des cellules selon plusieurs critères avec la fonction SOMMEPROD

La fonction SOMMEPROD permet généralement aussi de compter des cellules répondant à plusieurs critères dans Excel. Comme pour les sommes, il vous suffit simplement de supprimer l’argument de plage à additionner dans la formule.

Cas 1 : Compter des cellules selon plusieurs critères avec une logique ET

Ici, je souhaite compter les cellules dont le produit est « KTE » et dont la vente dépasse 200. Utilisez donc l’une des formules suivantes :

• Conseils: Dans la formule ci-dessus :
• A2:A12=«KTE» : représente la première plage de critères ainsi que la condition selon laquelle vous souhaitez effectuer le décompte.
• B2:B12>200 : représente la deuxième plage de critères ainsi que le critère selon lequel vous souhaitez effectuer le décompte.

Ensuite, appuyez sur la touche Entréepour obtenir le nombre correspondant au résultat :

Cas 2 : Compter des cellules selon plusieurs critères avec une logique OU

Pour compter le nombre de produits « KTE » et « KTO » dans la colonne A, appliquez cette formule :

Ensuite, appuyez sur la touche Entrée pour obtenir le nombre total des produits « KTE » et « KTO ». Voir la capture d’écran :

Cas 3 : Compter des cellules selon plusieurs critères combinant les logiques OU et ET

Pour compter le nombre de produits « KTE » et « KTO » dont la vente est supérieure à 200, appliquez la formule suivante :

Appuyez ensuite sur la touche Entréepour obtenir le nombre souhaité :

Exemple 4 : Calculer la moyenne pondérée avec la fonction SOMMEPROD

En général, il est facile et rapide de calculer la moyenne d’une plage de cellules. Toutefois, si vous souhaitez déterminer la moyenne d’une liste de valeurs affectées de poids différents, vous devez alors calculer une moyenne pondérée. Excel ne dispose pas de fonction dédiée à cette opération, mais la fonction SOMMEPROD vous permet de l’effectuer aisément.

Par exemple, j’ai un tableau statistique des notes d’un étudiant, chaque devoir étant affecté d’un coefficient différent, comme illustré ci-dessous.

Pour calculer la moyenne pondérée, utilisez la formule suivante :

Explication: Cette formule :SOMMEPROD(B2:B6; C2:C6) / SOMME(C2:C6) = (B2*C2+B3*C3+B4*C4+B5*C5+B6*C6)/(C2+C3+C4+C5+C6)

Appuyez ensuite sur la touche Entrée : la moyenne pondérée est calculée instantanément. Voir la capture d’écran :

 Autres articles utilisant la fonction SOMMEPROD :