Fonction SOMMEPROD Excel

Avec la fonction Excel SOMMEPROD, vous pouvez multiplier les colonnes de tableaux les unes avec les autres et obtenir la somme de ces multiplications.

Syntaxe

La syntaxe de la formule SOMMEPROD est :

= SOMMEPROD (Tableau1; [Tableau2]; [Tableau3]; … )

Où tableau1, tableau2, etc. sont des plages continues de cellules ou de tableaux dont vous souhaitez multiplier les éléments ligne par ligne, puis les ajouter.

La fonction en anglais est SUMPRODUCT.

Comment utiliser la formule SOMMEPROD à partir un exemple ?

Pour illustrer cette formule, l’exemple présenté correspond à l’objectif suivant : calculer le chiffre d’affaire réalisé à partir de 2 colonnes : quantité des ventes, prix de vente unitaire.

sommeprod-excel-synthaxe
sommeprod-excel-synthaxe

Cette formule unique avec  =SOMMEPROD(G4:G16;H4:H16) permet de remplacer :

  • Multiplication de 2 cellules pour toutes les pour toutes les lignes =H4*G4 (formule dans la colonne I)
  • Somme des résultats pour chaque ligne  =SOMME(I4:I14) (formule en I16)

sommeprod-excel-detail

En fait, la formule SOMMEPROD effectue les opérations mathématiques suivantes :

=G4*H4 + G5*H5 + … + G14*H14

Cette formule permet de gagner du temps et de la place sur la feuille Excel.

Imaginez combien de temps cela pourrait vous faire gagner si votre table ne contenait pas 11 lignes de données, mais 1000 lignes !

Le nombre minimum de tableaux est 1. Dans ce cas, une formule SOMMEPROD additionne simplement tous les éléments du tableau et renvoie la somme.

A partir Excel 2016, le nombre maximum de colonnes est de 255.

Remarques Concernant la construction d’Excel SOMMEPROD :

  • Tous les tableaux d’une formule SOMMEPROD doivent avoir le même nombre de lignes et de colonnes, sinon vous obtenez l’erreur #VALEUR.
  • Si une cellule contient des valeurs non numériques (texte …), elles seront traitées comme des zéros.

Comment construire la fonction Excel SOMMEPROD avec l’assistant ?

En partant d’un tableau qui a au moins 2 colonnes :

  • positionnez-vous dans la cellule où vous souhaitez afficher le résultat
  • puis tapez =SOMMEPROD(
  • et ensuite cliquer sur le bouton Fx qui est à droite de la barre de formule.
sommeprod-assistant-excel
sommeprod-assistant-excel

En cliquant sur Fx vous ouvrez l’assistant qui permet de saisir les différents tableaux à multiplier entre eux.

Sélectionner ensuite successivement les différentes zones à multiplier.

En cliquant sur OK, la fonction est validée.
=SOMMEPROD(D4:D14;E4:E14;F4:F14;G4:G14)

Différents exemples d’utilisation

Détecter les doublons

La fonction SOMMEPROD est une fonction polyvalente qui peut être utilisée pour effectuer diverses opérations, y compris le comptage des doublons dans une colonne.

La fonction SOMMEPROD est un outil idéal pour réaliser différentes tâches, notamment le dénombrement des doublons.

Voici les étapes pour utiliser SOMMEPROD afin de repérer puis supprimer les doublons :

  1. Choisissez une cellule vide où le résultat sera affiché
  2. Saisissez la formule suivante : =SOMMEPROD((A:A=A2)*(LIGNE(A:A)<>LIGNE(A2)))>0
  3. Appuyez sur la touche Entrée. Le résultat donnera le nombre de doublon dans la colonne A.

Il est important de noter que cette formule examine si la valeur située dans la cellule A2 apparaît plusieurs fois dans la colonne entière (via la référence A:A). Adaptez la plage de recherche en fonction de votre besoin d’analyse spécifique.

Fonction SOMMEPROD Excel avec plus de 2 colonnes

Vous disposez d’un tableau avec 4 colonnes et vous souhaitez obtenir le chiffre d’affaire en multipliant les 4 colonnes  ensemble :

sommeprod-plusieurs-colonnes
sommeprod-plusieurs-colonnes

SOMMEPROD avec des tableaux de même dimension

Vous disposez des ventes de différents produits sur 3 mois. Les prix changent d’un mois sur l’autre.

sommeprod-tableaux
sommeprod-tableaux

Pour établir la multiplication de la quantité avec son prix correspondant, il faut uniquement saisir la formule  =SOMMEPROD(D5:F9;D14:F18)

Où le premier tableau identifie les quantité et le second tableaux les prix.

Cela évite de monter le tableau suivant et de faire ensuite la somme des cellules.

sommeprod-dimension-simplification
sommeprod-dimension-simplification

Attention néanmoins, les matrices (tableaux) doivent avoir la même dimension (même nombre de lignes et de colonnes)

si on veut éviter l’erreur #VALEUR!

SOMMEPROD avec des tableaux de dimensions différentes

Vous disposez d’un tableau avec les ventes mensuelles et d’un tarif. Avec une seule formule, il est possible de calculer le chiffre d’affaire.

sommeprod-dimension-differente
sommeprod-dimension-differente

La formule est la suivante =SOMMEPROD(D5:F9*D14:D18)

Le premier paramètre est la zone des quantités qu’il faut multiplier par la zone tarif.

Les points d’attention pour que cette formule fonctionne sont :

  • Il faut que les produits du tableau Quantité et Prix soient identiques et dans le même ordre
  • Utiliser le signe multiplier entre les 2 zones (entre les parenthèses)

Les Messages d’erreur rencontrés

#VALEUR : vous avez certainement un nombre de lignes différent entre les différents colonnes que vous avez spécifiées, par exemple pour la colonne

Plus de détails sur la formule SommeProd Excel

Pour maîtriser cette formule, nous pouvons planifier un accompagnement personnalisé Excel pour la mettre en application sur vos fichiers et découvrir d’autres fonctionnalités Excel.

S’abonner
Notification pour
guest

0 Commentaires
Commentaires en ligne
Afficher tous les commentaires