Téléchargez l'exercice corrigé sur SOMMEPROD SI
Définition de la fonction SOMMEPROD
La fonction SOMMEPROD permet de multiplier des plages de données, appelé matrice, entre elles.
Exemple :
= SOMMEPROD((C2 : C7) * (D2 : C7))
Dans cet exemple, nous multiplions deux plages de donnée C2 :C7 et D2 :C7
Résultat
Le résultat sera le calcul suivant : C2*D2 + C3*D3 +… C7*D7
Cette solution vous fait gagner du temps pour réaliser des multiplications pondérées.
La fonction SOMMEPROD inclus la fonction SI
La fonction SOMMEPROD inclus la fonction SI dans ces paramètres.
Nous allons voir tout de suite un exemple qui nous permet d'utiliser la fonction somme prod avec des conditions similaires à la fonction SI.

Dans cet exemple, nous avons un tableau Excel RH qui représente des formations réalisées par une entreprise
Dans ce tableau, vous avez :
Le nom de la formation
Le lieu de la formation Paris Lyon Marseille
Le coût par jour
Le nombre de jours réalisés la formation
Le total par ligne
Nous allons calculer le coût total pour l’ensemble des formations avec la fonction somme prod.

Nous allons calculer le coût total pour l’ensemble des formations avec la fonction somme prod.
Pour cela, il suffit de calculer le nombre de jours multiplié par le coût par journée. La formule est la suivante =SOMMEPROD((C2:C7)*(D2:D7))
Qu’avons-nous fait ?
Avec =SOMMEPROD, vous indiquez entre parenthèses la première zone qui vous intéresse qui est le coût par jour. Cette zone s’appelle matrice.
Ensuite, vous le multipliez par la 2ᵉ matrice qui est le nombre de journées.
SOMMEPROD va automatiquement pour chacune des lignes multiplier le coût par jour, soit le nombre de jours.
Ainsi la cellule C2 sera multipliée avec D2 plus *avec D3-->
Résultat : C2*D2 + C3*D3 +… C7*D7
Nous voyons que le coût total des formations est de 16 800€ avec un simple calcul d'une fonction somme, on obtient bien la même chose.
La fonction SOMMEPROD avec SI EXCEL
Nous souhaitons maintenant savoir quel est le coût total des formations réaliser à Paris
Pour cela, vous récupérez votre ancienne fonction et vous avez bien vos deux matrices qui sont le coût par jour et le nombre multiplié par le nombre de jours.
Nous allons indiquer un critère supplémentaire en multipliant une 3e matrice qui correspond au site
Nous allons indiquer que cette zone doit être égale à Paris.
Paris doit être écrit entre guillemets "" avec un "P" majuscule sinon cela ne sera pas pris en compte
=SOMMEPROD((C2:C7)*(D2:D7)*(B2:B7="Paris"))
Cette 3ᵉ matrice *(B2:B7="Paris") permet de sélectionner le critère Paris sur le lieu de formation et de remplacer le critère SI de la fonction SI.

Il faut faire très attention lorsqu'on écrit du texte, la case, c'est-à-dire le fait que ça soit en majuscule ou minuscule est important.
Faites également attention si vous avez des espaces en trop ou des caractères spéciaux.
Si vous indiquez "paris" ou "Paris " (avec un espace après Paris) cela ne fonctionnera pas !
Nous pouvons voir que le résultat est de 9 600€ ce qui correspond bien après une revérification à l'ensemble des formations qui ont été réalisées à Paris.
La fonction SOMMEPROD avec SI EXCEL + un deuxième critère
Nous rajoutons une 2ᵉ condition, nous souhaitons désormais savoir quel est le coût total des formations Excel à Lyon.
Même principe, vous allez utiliser SOMMEPROD utilisez dans vos 2 premières matrices ce que vous souhaitez calculer, autrement dit le coût par jour fois le nombre de jours puis dans la 3ᵉ matrice, vous allez rajouter la condition du lieu, nous allons prendre la matrice B2 jusqu'à B7 et indiqué en texte « Lyon » entre guillemets.
Nous allons multiplier cette 3ᵉ matrice par une 4ᵉ qui est l'intitulé de la formation qui se situe entre B 2 et B 7 et nous allons indiquer que nous souhaitons avoir des formations Excel, nous écrirons donc égal « Excel » entre guillemets.
=SOMMEPROD((C2:C7)*(D2:D7)*(B2:B7="Lyon")*(A2:A7="Excel"))

Vous pouvez voir que le résultat est de 3800€ ce qui correspond bien à l'ensemble des formations Excel à Lyon.
Une simple vérification à travers des filtres Excel vous permet de vérifier ce total.
SOMMEPROD est une fonction très utile pour pouvoir réaliser des statistiques précises, elle inclut elle-même la fonction SI.
Vous pouvez donc réaliser des sommes sous conditions grâce à la fonction SOMMEPROD.
Notre Organisme de Formation :
Excel formation est une marque de Kronoscope organisme de formation certifié QUALIOPI, spécialiste en formation Excel, Excel VBA, Power Query et formation PowerBI pour entreprise et particulier.
Nous proposons des formations pour l'ensemble des niveaux :
Pour les sociétés, les formations ont lieu directement sur votre lieu de travail partout en France
Nous formons pour les particuliers dans le cadre de formation à distance CPF uniquement.
Nous avons des tests Excel gratuit en ligne pour évaluer votre niveau.
Testez votre connaissance sous Excel à travers un test Excel gratuit et avec correction !
Ce sont des tests réalisés par nos formateurs spécialistes en Excel et sont d'un d'un grand niveau de fiabilité.
Voici l'ensemble des villes où nous pouvons intervenir sans frais de déplacement ni d'hébergement, car nous avons des formateurs locaux.
Formation Excel à Paris, Formation Excel à Marseille, Formation Excel à Lyon, Toulouse, Nice, Nantes, Montpellier, Strasbourg, Bordeaux, Lille, Saint-Étienne, Dijon, Grenoble.
Nous formons pour des entreprises dans le cadre de formation formation excel intra entreprise.
Comentarios