Faire une somme conditionnelle sur un ou plusieurs critères avec SOMME.SI et SOMME.SI.ENS
Faire une somme conditionnelle sur un ou plusieurs critères avec SOMME.SI et SOMME.SI.ENS
Lorsque vous souhaitez calculer un total selon une condition précise, par exemple le chiffre d’affaires d’un commercial, le total d’un produit, le CA d'une région, ou les ventes d’un mois donné, il faut utiliser les fonctions SOMME.SI et SOMME.SI.ENS.
Elles permettent de faire des sommes conditionnelles, sans avoir à filtrer ou trier vos données.
C’est une fonction incontournable dans Excel pour l’analyse et le reporting.
Applications concrètes :
- Totaliser les ventes d’un commercial ou d’une région donnée.
- Calculer le CA d’un mois, d'une année ou d’un trimestre précis.
- Additionner les valeurs comprises entre deux dates.
- Faire des totaux croisés par produit, zone et période.
- Créer des tableaux de synthèse dynamiques sans utiliser de TCD.
SOMME.SI
La fonction SOMME.SI permet de faire une somme sur un seul critère.
Syntaxe :
SOMME.SI contient 3 arguments :
=SOMME.SI(plage_critère; critère; [plage_somme])
Détail des arguments :
- plage_critère
La plage dans laquelle Excel doit vérifier le critère (ex. la colonne des noms, des produits, etc.).
- critère
La condition à remplir pour additionner les valeurs correspondantes.
Elle peut être saisie entre guillemets (ex : “Nord”) ou être une référence de cellule (ex : D2).
- [plage_somme] (facultatif)
La plage des valeurs à additionner.
Si vous ne précisez pas cette plage, Excel additionne directement les valeurs de la plage_critère.
Exemple 1 : Somme sur un nom
Le tableau ci-dessous reprend le CA de 4 vendeurs dans 4 régions.
On veut connaitre le total des ventes de chaque vendeur.
On va commencer par le vendeur Damien.
Formule :
Plage : La plage du critère à chercher, donc la colonne des vendeurs
Critère : Le nom du vendeur : "Damien"
Le nom est à mettre entre guillemets.
Somme_plage : La colonne des valeurs à additionner : La colonne CA
Résultat :
Excel additionne les valeurs de la colonne "CA" uniquement pour les lignes où le nom est “Damien”.
Si vous voulez connaitre les ventes des 3 autres vendeurs, refaites la même fonction 3 fois en changeant le prénom.
Cela peut être chronophage, surtout s'il y a beaucoup de critères différents.
Il est donc préférable de choisir le critère dans une cellule et ensuite d'incrémenter la fonction une fois pour la recopier automatiquement avec les autres critères.
C'est ce que je vous montre dans l'exemple qui suit.
Exemple 2 : Critères dans une cellule
Reprenons le même tableau.
Nous allons en plus créer des lignes pour afficher les ventes de chaque vendeur.
Au lieu d'écrire "Damien" comme critère, on va directement sélectionner la cellule contenant Damien.
Ici Damien est dans la cellule G2, Sophie dans la cellule G3 etc
Puis on incrémentera la formule vers le bas pour avoir automatiquement le résultat pour les 3 autres vendeurs (en G3, G4, G5).
Cela permet de recopier automatiquement la formule vers les cellules du dessous.
Résultat :
Exemple 3 : Critère numérique
La fonction SOMME.SI permet aussi d'effectuer des calculs sur des critères numériques.
Le tableau ci-dessous est un extrait des salariés d'une entreprise fictive.
Le tableau contient 200 lignes, pour l'exemple je n'affiche que les colonnes Prénom, âge et salaire et les 20 premières lignes.
Nous voulons calculer la masse salariale des salariés de moins de 30 ans uniquement.
Formule :
Note : Pour un critère numérique, vous devez mettre le symbole < et la valeur entre guillemets : "<30"
Résultat :
Excel additionne les salaires lorsque l'âge est strictement inférieur à 30.
Et pour les autres tranches d'âges?
Pour calculer la masse salariale des autres tranches d'âges, par exemple les salariés entre 30 et 39 ans, on pourrait utiliser SOMME.SI 2 fois et faire une soustraction.
Une méthode plus simple est d'utiliser la 2ᵉ fonction de somme conditionnelle : SOMME.SI.ENS.
Cette fonction a le même rôle que SOMME.SI mais elle permet de faire des calculs sur plusieurs critères.
Ici nous avons en effet 2 critères : âges >=30 et âges <= 39.
Je vais vous montrer comment faire dans l'exemple 4.
SOMME.SI.ENS
La fonction SOMME.SI.ENS est une version améliorée de SOMME.SI :
Elle permet d’additionner des valeurs selon plusieurs critères simultanés (date, produit, région, vendeur, salarié, tranches dâges etc.).
Syntaxe :
=SOMME.SI.ENS(plage_somme; plage_critère1; critère1; [plage_critère2; critère2]; …)
La nuance par rapport à SOMME.SI, c'est que l'on met d'abord la plage de la somme comme 1ᵉʳ argument, et non plus à la fin.
Détail des arguments :
- plage_somme
La plage contenant les valeurs à additionner.
- plage_critère1, plage_critère2…
Les plages à analyser selon vos critères.
- critère1, critère2…
Les conditions à remplir pour que la valeur soit ajoutée au total.
Tous les critères doivent être vrais pour qu’une ligne soit incluse dans la somme.
Si vous avez 3 critères et un seul n'est pas respecté, la ligne ne sera pas ajoutée au total.
Exemple 3 : Somme selon deux critères
Reprenons le 1ᵉʳ tableau avec les vendeurs et les régions.
On va additionner le CA selon le vendeur et la région.
Calculons le CA de Damien sur la région Nord.
Formule :
=SOMME.SI.ENS(C2 :C16 ;A2 :A16 ; "Damien" ;B2 :B16 ; "Nord")
D'abord on sélectionne la colonne C (CA)
Puis la colonne du 1er critère : A (Vendeurs)
Le 1er critère : "Damien"
La colonne du 2nd critère : B (Régions)
Le second critère : "Nord"
Résultat :
Excel additionne les ventes de Damien uniquement sur la région Nord .
Note 1 :
Les 2 critères portent sur des colonnes différentes.
Note 2 :
Comme dans l'exemple 1, on peut sélectionner le nom du vendeur dans une cellule.
Note 3 :
On peut ajouter jusqu'à 256 critères.
Exemple 4 : Somme selon 2 critères numériques
Reprenons le tableau sur les âges et salaires.
On veut calculer la masse salariale sur la tranche d'âges 30-39.
Il y a 2 conditions :
Condition 1 : âge ">= 30" (ou ">29")
Condition 2 : âge "<= 39" (ou "<40")
Formule
D'abord on sélectionne la colonne H (Salaires)
Puis la colonne du 1er critère : F
Le 1er critère : ">=30"
La colonne du 2nd critère : F
Le second critère : "<40" (ou"<=39")
Résultat :
Note 1 :
J'ai mis comme critères ">=30" et "<40", je pouvais aussi mettre ">29" et "<=39"
Note 2 :
Ici les 2 critères portent sur la même colonne.
Critères textuels partiels
SOMME.SI et SOMME.SI.ENS permettent aussi de faire des additions sur des morceaux de texte.
Pour chercher des mots qui contiennent une partie de texte, utilisez le symbole * (astérisque).
Exemple 5 : Critères textuels partiels
Le tableau est un extrait de codes produits vendus par une entreprise.
Il y a 4 familles de produits : INF, BUR, ART. MAT.
On veut connaitre les ventes par famille.
On commence avec la catégorie informatique (INF).
Formules :
On met "INF*", cela additionne les valeurs de la colonne A pour tous les noms contenant le mot INF.
Résultat
On peut aussi sélectionner INF dans sa cellule (F2) et ajouter &"*".
On peut ensuite incrémenter pour avoir automatiquement les ventes des 3 autres catégories.
Note : On peut aussi utiliser un point d'interrogation (?) pour remplacer un seul caractère dans le texte.
SOMME.SI.ENS avec un seul critère
Vous pouvez aussi utiliser SOMME.SI.ENS même quand vous n'avez qu'un seul critère.
Reprenons le tout 1ᵉʳ exemple avec Damien :
On a écrit la formule :
Cela fonctionne aussi si vous utilisez SOMME.SI.ENS :
En anglais :
SOMME.SI : SUMIF
SOMME.SI.ENS : SUMIFS
Fonctions associées : Calcul de moyenne, plus grande et plus petite valeur.
De la même manière que l'on fait une somme sur des critères, on peut faire la moyenne avec les fonctions MOYENNE.SI et MOYENNE.SI.ENS.
La syntaxe est exactement la même.
Vous pouvez reprendre les exemples de l'article et remplacer SOMME.SI et SOMME.SI.ENS par MOYENNE.SI et MOYENNE.SI.ENS.
Cela vaut aussi si vous voulez retrouver la plus grande et la plus petite valeur selon un ou plusieurs critères.
Utilisez les fonctions MAX.SI.ENS, MIN.SI.ENS. (MAX.SI et MIN.SI n'existent plus).
Là aussi la syntaxe est la même.
Enfin , il existe 2 fonctions pour compter des valeurs selon un ou plusieurs critères :
Vous pouvez retrouver l'article dédié ici.
Voilà, cet article sur les fonctions SOMME.SI et SOMME.SI.ENS est terminé.
Si cela vous a plu, vous pouvez le commenter et vous abonner au blog si ce n’est pas encore fait, pour recevoir du contenu exclusif réservé aux membres et progresser sur Excel.
Il vous suffit de renseigner votre prénom et votre adresse mail dans le formulaire ci-dessous ou dans la pop-up qui s’affiche parfois.
À bientôt sur le blog Maîtrisez Excel.
Steeve
