La fonction SOMMEPROD

La fonction Excel SOMMEPROD est un outil puissant et très intéressant d’Excel. La définition de l’aide de votre tableur favori (ou pas mais vous n’avez peut-être pas le choix…) est la suivante:

« Multiplie les valeurs correspondantes des matrices spécifiées et calcule la somme de ces produits. »

Ouais… OK concrètement ça veut dire quoi ?

Fonction Excel SOMMEPROD en pratique

 

pour connaître la valeur totale des ventes de fruits ci-dessus, avec la fonction SOMMEPROD, on peut faire tout le calcul en une fois.

La syntaxe de la formule ici sera :

=SOMMEPROD(A3:B4;D3:E4)

Le résultat me donnera 184, ce qui est celui que j’obtiens en multipliant ligne à ligne les quantités avec les prix et en additionnant le tout. Bien sûr, l’intérêt est relatif avec 2 lignes mais le principe est le même pour 3000 lignes, et c’est aussi simple. Attention néanmoins, les matrices doivent avoir la même dimension si on veut éviter l’erreur #VALEUR!

Pourtant, ce n’est pas comme ça que j’ai découvert cette fonction. En fait, je l’ai utilisée, un peu comme la fonction BDSOMME mais sans passer par un tableau de critère (OK, on verra cette fonction plus tard).

En effet, quand les éléments d’une matrice ne sont pas des numériques, Excel leur affecte la valeur 0 et on peut s’en servir comme critères. Exemple:

 

 

Ici, si je veux connaître les ventes en France, sans autre critère, je pourrais utiliser la fonction SOMME.SI. Mais si je veux en plus, connaître les ventes en France de Pommes sur le mois de janvier, je peux utiliser aussi la fonction SOMMEPROD. La syntaxe serait la suivante:

=SOMMEPROD((A2:A6="France")*(B2:B6="Pomme")*(C2:C6="Janvier")*(D2:D6))

Le résultat ici sera 100 (OK, facile…). Si jeux connaître les ventes d’oranges en février, il faudra écrire:

=SOMMEPROD((B2:B6="Orange")*(C2:C6="Février")*(D2:D6))

Le résultat sera de 125. On peut bien sûr remplacer « Orange » par une cellule contenant cette valeur (ici $B$3). Vous l’aurez compris, la syntaxe de cette formule correspond à :

=SOMMEPROD( (Critère 1)*(Critère2)*...*(Somme de la plage voulue))

Vous conviendrez que cette fonction est très pratique à intégrer dans des synthèses et sans être obligé de garder un formalisme identique à la base de donnée d’origine. Je viens d’en faire l’expérience dans un tableau de synthèse des ventes, à partir d’une extraction comportant pour l’instant 12 000 lignes et qui devrait finir à 20 000 lignes d’ici la fin de l’année. L’important étant de savoir quels critères utiliser.

Petite précision: la version 2007 d’Excel vous offre 255 matrices possibles (ou critères) dans cette fonction. De quoi vous laisser de la marge pour peaufiner vos sélections.

Bons calculs !

Pourrait aussi vous intéresser: Liste de toutes les fonctions Excel!