Cliquer ici pour télécharger le fichier
Principe : La formule SOMMEPROD permet de faire la somme des produits des matrices indiquées.
= SOMMEPROD(matrice1 ; [matrice2] ; [matrice3] …)
- Matrice pour lesquelles on souhaite multiplier les valeurs entre elles et faire la somme des produits.
Remarques :
- Il est possible de réaliser d’autres opérations que la multiplication : Pour cela il faut remplacer les points-virgules entre les matrices par les opérateurs souhaités (- ; + ; * ; /) => Une fois toutes les opérations calculées les résultats sont additionnés.
- Il est possible d’ajouter des conditions aux calculs effectués : Pour cela il faut mettre entre parenthèses les matrices et insérer entre les matrices les opérateurs souhaités.
Exemple 1 : Somme de multiplications.
On a un tableau qui récapitule les ventes d’une entreprise et on souhaite calculer le total du CA généré.
= SOMMEPROD(D2:D10 ; E2:E10) OU = SOMMEPROD(D2:D10 * E2:E10)
Détail du calcul : 2 950 €
= 5*100 + 2*50 + 3*100 + 4*100 + 6*50 + 5*100 + 3*50 + 6*50 + 4*100
Exemple 2 : Somme de multiplications avec conditions.
Suite de l’exemple 1 excepté que cette fois-ci on souhaite calculer le total du CA généré uniquement dans la ville de LYON sur les ventes de chaises.
= SOMMEPROD((B2:B10= »LYON ») * (C2:C10= »Chaise ») * (D2:D10) * (E2:E10))
Détail du calcul : 250 €
= 2*50 + 3*50
Exemple 3 : Somme de multiplications de matrices de tailles différentes.
On a la liste des notes obtenues par les étudiants d’une classe et on souhaite calculer la moyenne générale sur l’ensemble des épreuves.
= SOMMEPROD(B2*B3:B10 + C2*C3:C10 + D2*D3:D10) /(B2+C2+D2) /8
Détail du calcul : 13,26
= ((5*20 + 5*19 + 5*12 + 5*6 + 5*5 + 5*18 + 5*12 + 5*14) + (2*16 + 2*8 + 2*11 + 2*7 + 2*15 + 2*10 + 2*10 + 2*19) + (3*18 + 3*19 + 3*12 + 3*16 + 3*14 + 3*13 + 3*8 + 3*13)) / (5+2+3) / 8
*(5 + 2 + 3) = 10 => Somme des coefficients.
*8 => Nombre d’étudiants.
Exemple 4 : Opérations multiples.
On a un tableau qui récapitule les ventes d’une entreprise et on souhaite calculer le total de la marge générée (Qx(PU-CV)).
= SOMMEPROD(B2:B6 * (C2:C6 – D2:D6))
Détail du calcul : 5 430 €
= 10*(250-85) + 5*(200-70) + 8*(500-180) + 12*(50-15) + 15*(15-5)
Exemple 5 : Somme avec conditions en ligne et en colonne.
On a un tableau qui récapitule les chiffres d’affaires mensuels des 3 dernières années.
=> on souhaite retrouver le chiffre d’affaires réalisé sur une période précise en fonction du mois (en ligne) et de l’année (en colonne).
=> Le mois et l’année sont sélectionnés à partir d’une liste déroulante :
Calcul pour l’année :
= SOMMEPROD((B1:D1=H3) * (B2:D13)) = 145 794 €
Calcul pour le mois et l’année :
= SOMMEPROD((A2:A13=F3) * (B1:D1=H3) * (B2:D13)) = 9 742 €
Calcul pour le trimestre et l’année :
=> Dans ce cas il faut créer une nouvelle colonne pour pouvoir indexer le numéro du trimestre en fonction du mois.
=> Cela a pour effet de décaler les colonnes de choix :
= SOMMEPROD((B2:B13=H3) * (C1:E1=I3) * (C2:E13)) = 35 572 €
=> Voir aussi la formule SOMME.SI.ENS
