La fonction SUMPRODUIT dans Excel est une fonction très polyvalente qui donnera des résultats différents selon la façon dont les arguments de la fonction sont entrés.
Normalement, comme son nom l’indique, SUMPRODUCT multiplie les éléments d’un ou de plusieurs tableaux pour obtenir leur produit, puis additionne ou additionne les produits ensemble.
Toutefois, en ajustant la syntaxe de la fonction, elle peut être utilisée pour additionner uniquement les données dans les cellules qui répondent à des critères spécifiques.
Depuis Excel 2007, le programme contient deux fonctions – SUMIF et SUMIFS – qui permettent d’additionner les données dans des cellules qui répondent à un ou plusieurs critères définis.
Cependant, il est parfois plus facile de travailler avec le SUMPRODUIT lorsqu’il s’agit de trouver plusieurs conditions relatives à la même gamme, comme le montre l’image ci-dessus.
Contenu
Syntaxe de la fonction SUMPRODUCT pour additionner les cellules
La syntaxe utilisée pour obtenir SUMPRODUIT pour additionner les données dans des cellules qui répondent à des conditions spécifiques est :
= SUMPRODUIT([condition1] * [condition2] * [array])
condition1, condition2 – les conditions qui doivent être remplies avant que la fonction ne trouve le produit du tableau.
array – une série de cellules contiguës
Exemple : Récapitulation des données dans des cellules qui remplissent plusieurs conditions
L’exemple de l’image ci-dessus ajoute les données dans les cellules de la gamme D1 à E6 qui sont comprises entre 25 et 75.
Entrer dans la fonction SUMPRODUIT
Comme cet exemple utilise une forme irrégulière de la fonction SUMPRODUIT, la boîte de dialogue de la fonction ne peut pas être utilisée pour entrer la fonction et ses arguments. La fonction doit plutôt être saisie manuellement dans une cellule de la feuille de calcul.
- Cliquez sur la cellule B7 dans la fiche de travail pour en faire la cellule active ;
- Entrez la formule suivante dans la cellule B7 :=SUMPRODUCT(($A$2:$B$6>25)*($A$2:$B$6<75)*(A2:B6))
- La réponse 250 doit apparaître dans la cellule B7
- La réponse a été obtenue en additionnant les cinq chiffres de la fourchette (40, 45, 50, 55 et 60) qui se situent entre 25 et 75. Le total est de 250
Décomposer la formule des SUMPRODUITS
Lorsque des conditions sont utilisées pour ses arguments, SUMPRODUCT évalue chaque élément du tableau par rapport à la condition et renvoie une valeur booléenne (VRAI ou FAUX).
Aux fins des calculs, Excel attribue une valeur de 1 pour les éléments de tableau qui sont VRAIS (remplissent la condition) et une valeur de 0 pour les éléments de tableau qui sont FAUX (ne remplissent pas la condition).
Par exemple, le chiffre 40 :
- est VRAI pour la première condition, donc une valeur de 1 est attribué dans le premier tableau ;
- est VRAI pour la deuxième condition, de sorte qu’une valeur de 1 est attribué dans le deuxième tableau.
Le chiffre 15 :
- est FAUSSE pour la première condition, donc une valeur de 0 est attribué dans le premier tableau ;
- est VRAI pour la deuxième condition, de sorte qu’une valeur de 1 est attribué dans le deuxième tableau.
Les uns et les zéros correspondants dans chaque tableau sont multipliés ensemble :
- Pour le nombre 40 – nous avons 1 x 1, ce qui donne une valeur de 1 ;
- Pour le nombre 15 – nous avons 0 x 1, ce qui donne une valeur de 0.
Multiplication des uns et des zéros par la gamme
Ces uns et ces zéros sont ensuite multipliés par les nombres de l’intervalle A2 : B6.
Cela nous permet de connaître les chiffres qui seront additionnés par la fonction.
Cela fonctionne parce que :
- 1 fois tout nombre est égal au nombre initial
- 0 fois tout nombre est égal à 0
On finit donc par :
- 1 * 40 = 40
0 * 15 = 0
0 * 22 = 0
1 * 45 = 45
1 * 50 = 50
1 * 55 = 55
0 * 25 = 0
0 * 75 = 0
1 * 60 = 60
0 * 100 = 0
Synthèse des résultats
SUMPRODUIT résume ensuite les résultats ci-dessus pour trouver la réponse.
40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0 = 250
