Tout savoir sur la fonction SOMMEPROD sur Excel ?

SOMMEPROD et SI sont deux des fonctions Excel les plus puissantes et polyvalentes. Chacune a son propre domaine d’application. Toutefois, leur combinaison permet d’aller encore plus loin dans l’analyse de données complexes. On peut alors appliquer des conditions précises à des calculs de sommes.

La fonction SOMMEPROD, comme son nom l’indique, permet de multiplier des plages de données entre elles avant d’en faire la somme. Quant à la fonction SI, elle permet renvoyer des résultats différents en fonction de la véracité de ces conditions. Mais que se passe-t-il lorsqu'on décide d’associer les deux ? Ce tuto va vous guider à travers l’utilisation combinée de SOMMEPROD SI Excel. Inscrivez-vous à notre formation Excel pour débutants si vous voulez un guide plus complet et détaillé

Vidéo : comment utiliser la fonction SOMMEPROD SI sur Excel ?

Dans cette vidéo, notre formateur Célestin vous explique pas à pas comment maîtriser SOMMEPROD SI, même si vous débutez.

On commence par les bases, puis on passe aux conditions avancées, le tout avec des exemples concrets. Après ça, vous saurez utiliser cette fonction pour gagner un temps précieux dans vos analyses de données.

Comprendre la fonction SOMMEPROD SI Excel

SOMMEPROD est utilisée pour effectuer des calculs conditionnels et des analyses avancées sur des tableaux de données.

Définition et principe de fonctionnement

La fonctionSOMMEPROD permet de multiplier les éléments de plusieurs plages de cellules entre eux, puis d’additionner les résultats obtenus. Elle est particulièrement utile quand on calcule un total pondéré, lorsqu’on fait des calculs conditionnels ou encore des analyses financières.

Syntaxe de SOMMEPROD

Voici la structure de base de la fonction :

=SOMMEPROD(plage1;plage2;…)

• Plage1, Plage2, … : Ce sont des plages de cellules qui seront multipliées entre elles.
• La fonction multiplie les éléments ligne par ligne, puis additionne les résultats.

Exemple : Calculer un total pondéré

Voici un tableau contenant les ventes d’un magasin :

Si vous voulez calculer le chiffre d’affaires total, vous devez multiplier chaque quantité vendue par son prix unitaire, puis additionner le tout.

Voici la formule SOMMEPROD correspondante :

=SOMMEPROD(B2:B4;C2:C4)

Explication du calcul :

• (10 × 5) + (8 × 7) + (15 × 4) = 50 + 56 + 60 = 166

SOMMEPROD simplifie donc les calculs qui nécessiteraient normalement une colonne intermédiaire et une SOMME classique.

Rappel sur la fonction SI dans Excel

La fonction SI est l’une des plus utilisées dans Excel. Elle permet de tester une condition et renvoyer des valeurs différentes en fonction du résultat.

Définition et fonctionnement de la fonction SI

La fonction SI permet d’évaluer une condition et de retourner une valeur si celle-ci est vraie, et une autre valeur si elle est fausse.

Syntaxe de la fonction SI

Voici la structure de la fonction :

=SI(condition;valeur_si_vrai;valeur_si_faux)

• condition : Un test logique (ex. : A1>10, B2="Oui").
• valeur_si_vrai : Ce qui est renvoyé si la condition est vraie.
• valeur_si_faux : Ce qui est renvoyé si la condition est fausse.

Exemple 

Imaginons une liste de produits et leurs stocks. Vous voulez afficher “Stock faible” si la quantité est inférieure à 10 et “Stock OK” sinon.

Formule à utiliser en C2 :

=SI(B2<10;"Stock faible";"Stock OK")

Résultat :

• Produit A → Stock faible
• Produit B → Stock OK
• Produit C → Stock faible

Associer SOMMEPROD et SI : pourquoi et comment ?

Maintenant que nous avons vu le fonctionnement de SOMMEPROD et SI, voyons comment et à quel moment les combiner ?

Pourquoi combiner SOMMEPROD et SI ?

En théorie, on pourrait utiliser SOMME.SI ou SOMME.SI.ENS pour certains calculs conditionnels, mais ces fonctions ont des limitations :

• Elles ne permettent pas d’appliquer des calculs personnalisés avant l’addition.
• Elles sont limitées à un seul critère ou à des comparaisons simples.
• Elles ne fonctionnent pas avec plusieurs conditions appliquées à différentes colonnes nécessitant une multiplication de valeurs.

Avec SOMMEPROD, on peut appliquer plusieurs conditions simultanément et même effectuer des calculs sur des valeurs filtrées.

Syntaxe de SOMMEPROD avec une condition SI

La clé pour utiliser SI avec SOMMEPROD, c’est d’écrire la condition directement dans SOMMEPROD sous forme de test logique qui renvoie 1 (vrai) ou 0 (faux).

=SOMMEPROD((condition)*plage_de_calcul)

Ou, avec plusieurs critères :

=SOMMEPROD((condition1)*(condition2)*plage_de_calcul)

L’astuce ici est que les conditions évaluées comme VRAI ou FAUX sont transformées en 1 ou 0, ce qui permet à SOMMEPROD de ne prendre en compte que les valeurs respectant les conditions.

Exemples

 Somme des ventes d’un produit spécifique

Voici un tableau de ventes avec des produits et des quantités vendues.

Si nous voulons calculer le chiffre d’affaires total uniquement pour le produit A, nous pouvons utiliser :

=SOMMEPROD((A2:A4="A")*B2:B4*C2:C4)

Si en validant la formule, vous obtenez une erreur, il faut taper Ctrl+Shift+Entrée.

Explication :

• A2:A4="A") renvoie {1;0;1} (1 si le produit est "A", 0 sinon).
• B2:B4*C2:C4 effectue la multiplication des quantités et prix unitaires.
• Seules les lignes correspondant à "A" sont comptabilisées.

Résultat :

• (10 × 5) + (15 × 4) = 50 + 60 = 110 €

Somme avec plusieurs critères

Prenons un autre exemple. Nous voulons calculer le chiffre d’affaires uniquement pour le produit A. MAIS seulement si la quantité vendue est supérieure à 10.

=SOMMEPROD(SI(A2:A4="A")*(B2:B4>10)*B2:B4*C2:C4)

Explication :

• (A2:A4="A") filtre les produits "A".
• (B2:B4>10) filtre les quantités supérieures à 10.
• Seules les lignes respectant ces deux conditions sont multipliées et additionnées.

Résultat :

• (15 × 4) = 60 € (seule la deuxième ligne de "A" est prise en compte).

Comptage conditionnel avec SOMMEPROD

SOMMEPROD ne sert pas seulement à faire des sommes, mais aussi à compter des éléments respectant plusieurs conditions.

Si nous voulons compter le nombre de ventes où la quantité dépasse 10, nous pouvons utiliser :

=SOMMEPROD((B2:B4>10)*1)

Explication :

• (B2:B4>10) renvoie {0; 0; 1} (1 pour les quantités >10).
• SOMMEPROD additionne ces valeurs : 1 vente dépasse 10 unités.

Vous souhaitez maîtriser l’utilisation des fonctions SOMMEPROD SI Excel ? Ou vous avez envie d’explorer toutes les  fonctionnalités de MS Excel et d’approfondir vos compétences? Rendez-vous à notre formation Excel à Montauban. Faites-vous assisté par des formateurs professionnels.