Utilisation de base | Utilisation avancée
Pour calculer la somme des produits des nombres correspondants dans une ou plusieurs plages, utilisez La puissante fonction SUMPRODUCT d'Excel.
Utilisation de base
1. Par exemple, la fonction SOMMEPROD ci-dessous calcule le montant total dépensé.
Explication : la fonction SOMMEPROD effectue ce calcul : (2 * 1000) + (4 * 250) + (4 * 100) + (2 * 50) = 3500.
2. Les plages doivent avoir les mêmes dimensions ou Excel affichera la #VALUE ! Erreur.
3. La fonction SOMMEPROD traite toutes les entrées qui ne sont pas numériques comme s'il s'agissait de zéros.
4. Si vous fournissez une seule plage, la fonction SOMMEPROD produit exactement le même résultat que la fonction SOMME.
Utilisation avancée
La fonction SOMMEPROD est une fonction extrêmement polyvalente et peut produire le même résultat que de nombreuses fonctions intégrées dans Excel et même des formules matricielles !
1a. Par exemple, la fonction COUNTIF ci-dessous compte le nombre de cellules contenant exactement l'étoile.
1b. La fonction SOMMEPROD ci-dessous produit exactement le même résultat.
Explication : --(A1:A7="star") se réduit à la constante de tableau suivante :
--{VRAI;FAUX;FAUX;FAUX;FAUX;VRAI;FAUX}
Le double négatif -- contraint ces booléens à des 1 et des 0 (TRUE=1, FALSE=0). Résultat:
{1;0;0;0;0;1;0}
Cette constante matricielle est utilisée comme argument pour la fonction SOMMEPROD, donnant un résultat de 2.
1c. La fonction COUNTIF ci-dessous compte le nombre de cellules qui contiennent exactement étoile + 1 caractère. Un point d'interrogation (?) correspond exactement à un caractère.
1d. La fonction SOMMEPROD n'est pas parfaite ! Vous ne pouvez pas utiliser de caractères génériques (? et *) lorsque vous utilisez la fonction SOMMEPROD.
2a. La formule matricielle ci-dessous compte le nombre de caractères dans une plage de cellules.
Remarque : terminez une formule matricielle en appuyant sur CTRL + MAJ + ENTRÉE. Excel ajoute les accolades {}.
2b. La fonction SOMMEPROD ci-dessous produit exactement le même résultat.
Remarque : la constante de tableau {9;4;6;5} est utilisée comme argument pour la fonction SOMMEPROD, donnant un résultat de 24. La fonction SOMMEPROD gère les tableaux de manière native, vous n'avez donc pas à terminer en appuyant sur CTRL + MAJ + ENTRER. C'est l'un des plus gros avantages de la fonction SOMMEPROD car les accolades {} confondent de nombreux débutants Excel !
3a. La formule matricielle ci-dessous additionne les ventes en 2021-2022.
3b. La fonction SOMMEPROD ci-dessous produit exactement le même résultat.
Explication : (ANNÉE(A1:A5)=2018)*B1:B5 se réduit à :
({2018;2017;2018;2017;2017}=2018)*{10;1;7;20;3} et cela se réduit à :
{VRAI;FAUX;VRAI;FAUX;FAUX}*{10;1;7;20;3}
Nous n'avons pas besoin d'un double négatif (voir exemple 1b) car l'opérateur de multiplication * contraint automatiquement les booléens à des 1 et des 0 (VRAI=1, FAUX=0). Résultat:
{10;0;7;0;0}
Cette constante matricielle est utilisée comme argument pour la fonction SOMMEPROD, donnant un résultat de 17.
