SOMME.SI.ENS Excel: Guide Complet et Pratique

La fonction Excel SOMME.SI.ENS permet d'additionner des cellules correspondant à plusieurs critères: SOMME SI ENSEMBLE de conditions est rempli. Il s'agit d'un dérivé de la fonction Excel plus connue: la SOMME.SI…

SOMME.SI.ENS peut être utilisée pour additionner des valeurs lorsque les cellules correspondantes répondent en même temps à des critères (jusqu'à 127!) basés sur des dates, des nombres ou du texte. Cette fonction prend en charge les opérateurs logiques (>,<,<> et =) et les caractères génériques (* et ?) pour une correspondance partielle.

Syntaxe de la fonction SOMME.SI.ENS

Et voici la syntaxe de la fonction à utiliser dans Excel:
=SOMME.SI.ENS(Plage_somme;Plage_critères1;Critères1;Plage_critères2;Critères2;…;Plage_critères127;Critères127)
Pour information, l'équivalent en anglais de SOMME.SI.ENS est SUMIFS

Les arguments de la fonction SOMME.SI.ENS

La fonction SOMME.SI.ENS contient 3 arguments obligatoires et jusqu'à 126 paires d'arguments optionnels. Les voici en détail…

• Plage_somme: c'est la plage qui contient des valeurs qui doivent être additionnées. La plage de cellules peut être indiquée sous la forme d'adresse (ex. C2:C200) ou en tant que plage nommée (ex. MontantsDeVente). Cet argument est obligatoire.
• Plage_critères1: ceci est la plage avec les données qui doivent remplir le première critère. Il peut s'agir des données de texte, des dates, des nombres. À nouveau, il est possible d'indiquer cette plage comme une adresse ou comme le nom d'une plage nommée. Cet argument est obligatoire.
• Critères1: Attention, c'est ici que cela se joue! Il s'agit de la condition que les valeurs de Plage_critères1 doivent remplir pour être reprises dans le résultat final. Ce critère peut contenir des dates, des nombres, des textes, des valeurs booléennes ( VRAI / FAUX ), une formule ou une référence à une cellule. Les textes et les dates doivent être entre parenthèses, contrairement aux nombres et les booléens. Si vous omettez l'opérateur, c'est l'opérateur = qui sera utilisé. Si vous souhaitez utiliser d'autres opérateurs, l'argument DOIT se trouver entre parenthèses. Voir exemples d'utilisation plus loin. Pour les textes, vous pouvez utiliser les caractères de remplacement ? et * (voir également dans les exemples).
• Plage_critères2: ceci est la plage avec les données qui doivent remplir le deuxième critère. Cet argument se comporte de la même manière que Plage_critères1 avec la différence que celui-ci est optionnel.
• Critères2: la deuxième condition que les données doivent remplir. Cet argument se comporte de la même manière que Critères1 sans être obligatoire.
• ...
• Plage_critères127: La dernière plage de données à pouvoir être utilisée. Se comporte exactement comme Plage_critères2.
• Critères127: La dernière condition à pouvoir être utilisée. Se comporte exactement comme Critères2

La valeur renvoyée par la fonction SOMME.SI.ENS – "Somme Si Ensemble"

Le résultat de la fonction SOMME.SI.ENS est représenté par une somme des valeurs qui remplissent TOUTES les conditions listées dans la fonction.

Si une des conditions n'est pas remplie, la valeur ne sera pas incluse dans la somme.

Exemples pratiques: formule SOMME.SI.ENS Excel

On va maintenant se pencher sur les différents cas pratiques de l'utilisation de cette fonction. Pour cela, j'ai préparé un fichier Excel avec des données sur lequel nous allons tester les différentes possibilités.
Vous pouvez télécharger le fichier DÉMO ici.

Il s'agit d'un tableau reprenant des ventes de 3 produits différents (stylo, cahier, crayon) de deux manières différentes (an magasin et en ligne) sur une période de temps.

Cas le plus simple: deux conditions basiques

Commençons par un cas simple: Faire la somme des ventes des stylos faites en ligne.

Pour cela, nous aurons alors deux conditions: article = "stylo" et type de vente = "En ligne".

La formule sera ensuite la suivante:

=SOMME.SI.ENS(F2:F14;B2:B14;"stylo";C2:C14;"En ligne") et le résultat sera de 171,80 €

Si vous souhaitez placer "stylo" et "En ligne" dans des cellules (disons par exemple H3 et H6) plutôt que dans la formule pour un changement plus facile, la formule sera la suivante:

=SOMME.SI.ENS(F2:F14;B2:B14;H3;C2:C14;H6)

Et maintenant un exemple avec un argument numérique…

Si vous souhaitez additionner les montants totaux des ventes en ligne dont le prix unitaire a été de 9 €, voici la formule:

=SOMME.SI.ENS(F2:F14;E2:E14;9;C2:C14;"En ligne")

Comme vous pouvez le remarquer, le 3^ème argument a la valeur 9

SOMME.SI.ENS avec des opérateurs

Maintenant quelques exemples d'utilisation avec les différents opérateurs:

1. Somme des ventes en ligne avec un total supérieur à 10 €
   • La formule: =SOMME.SI.ENS(F2:F14;C2:C14;"En ligne";F2:F14;">10″)
   • L'argument avec opérateur: ">10" (entouré de guillemets!)
   • Résultat: 259,80 €
   • Remarque: comme vous pouvez voir, on peut utiliser la même colonne pour une condition et pour faire la somme souhaité (ici les totaux des ventes)
2. Somme des ventes d'articles autres que stylos vendus en ligne
   • La formule: =SOMME.SI.ENS(F2:F14;C2:C14;"En ligne";B2:B14;"<>stylo")
   • L'argument avec opérateur: "<>stylo"
   • Résultat: 100,00 €
3. La somme des ventes de stylos où on a vendu au maximum 5 stylos d'un coup
   • La formule: =SOMME.SI.ENS(F2:F14;B2:B14;"stylo";D2:D14;"<=5″)
   • L'argument avec opérateur: "<=5"
   • Résultat: 27,80 €

Fonction SOMME.SI.ENS avec des comparaisons basées sur une valeur dans une autre cellule

Un cas très répandu mais qui prête souvent à confusion, c'est quand la comparaison ne se base pas sur une valeur fixe mais sur le contenu d'une cellule. Par exemple "si la valeur est plus grande que la valeur dans la cellule C1".

La règle est simple: vous devez mettre l'opérateur entre guillemets, suivi de & et du nom de la cellule. Donc par exemple ">" & C1. Attention donc à ne pas mettre toute l'expression entre guillemets – cela ne va pas marcher! (par exemple: ">C1" ne fonctionnera pas)

Voici quelques exemples pratiques:

1. La somme des ventes en ligne avec un total supérieur à la valeur de la cellule "K1"
   • La formule: =SOMME.SI.ENS(C2:C27;"En ligne";F2:F27;">" & K1)
   • L'argument avec opérateur: ">" & K1 (Attention! Comme expliqué plus haut, uniquement l'argument est entre guillemets!)
2. Nombre des ventes en ligne où on a vendu autre chose que l'article dont le nom se trouve dans la cellule "P1"
   • La formule: =SOMME.SI.ENS(C2:C27;"En ligne";B2:B27;"<>" & P1)
   • L'argument avec opérateur: "<>" & P1

Conditions avec des caractères génériques ? et *

Maintenant, penchons-nous sur les conditions textuelles avec des caractères génériques qui permettent de faire la somme des cellules basée sur des textes similaires:

1. Somme des ventes en ligne des produits commençant par la lettre c
   • La formule: =SOMME.SI.ENS(F2:F14;C2:C14;"En ligne";B2:B14;"c*")
   • L'argument avec opérateur: "c*" (entouré de guillemets)
   • Résultat: 100,00 €
   • Remarque: l'astérisque remplace un nombre non spécifié de caractères
2. Somme des ventes d'articles dont le nom est composé de 6 caractères vendus en magasin
   • La formule: =SOMME.SI.ENS(F2:F14;C2:C14;"Magasin";B2:B14;"??????")
   • L'argument avec opérateur: "??????"
   • Résultat: 57,50 €
   • Remarque: l' ? remplace exactement 1 caractère. C'est pour cela qu'ici, on va utiliser la condition avec 6 points d'interrogation
3. La somme des ventes supérieures à 15,00 € des produits dont le nom contient la lettre "y"
   • La formule: =SOMME.SI.ENS(F2:F14;F2:F14;">15″;B2:B14;"*y*")
   • L'argument avec opérateur: "*y*"
   • Résultat: 230,30 €

Utilisation de SOMME.SI.ENS avec des DATES

Si vous souhaitez utiliser les dates dans vos conditions, vous avez deux possibilités: pour une condition "avant une date" ou "après une date", il vous suffit d'utiliser simplement les opérateurs numériques < ou > (éventuellement <= ou => ).

Pour une durée de temps délimitée (comme par exemple: "tout le mois de janvier 2022"), vous devrez utiliser deux conditions différentes avec les deux dates qui délimite la période souhaitée. Attention donc, les caractères génériques * et ? ne fonctionnent pas avec des dates. Oubliez donc des conditions comme "*-01-2022″…

1. Somme des ventes supérieures à 30,00 € après le 20/12/2021
   • La formule: =SOMME.SI.ENS(F2:F14;F2:F14;">30″;A2:A14;">20/12/2021″)
   • L'argument avec opérateur: ">20/12/2021" (entourés de guillemets car contient un opérateur)
   • Résultat: 77,50 €
   • Remarque: Excel reconnaît qu'il s'agit des dates d'après leur format. Comme Excel utilise plusieurs formats de dates, il en reconnaît plusieurs également. Si on avait utilisé ">20-12-2021" à la place de ">20/12/2021", la formule aurait fonctionné de la même manière
2. Somme des ventes en ligne faites en novembre 2021
   • La formule: =SOMME.SI.ENS(F2:F14;C2:C14;"En ligne";A2:A14;">=1/11/2021″;A2:A14;"<=30/11/2021″)
   • Les arguments avec opérateur: ">=1/11/2021" et "<=30/11/2021" (entourés de guillemets car contiennent un opérateur)
   • Résultat: 58,00 €
   • Remarque 1: vous remarquez que nous avons utilisé 3 conditions différentes
   • Remarque 2: les deux conditions de dates – "après 1/11/2021 inclus" et "avant 30/11/2021 inclus" sont utilisées avec la logique ET ce qui permet de délimiter la période souhaité
3. Le total des ventes des crayons en dehors de la période 1/12/2021 à 15/12/2021
   • La formule: =SOMME.SI.ENS(F2:F14;B2:B14;"crayon";A2:A14;"<1-12-2021″)+SOMME.SI.ENS(F2:F14;B2:B14;"crayon";A2:A14;">15-12-2021″)
   • Les arguments avec opérateur: "<1-12-2021" et ">15-12-2021"
   • Résultat: 58,50 €
   • Remarque: comme les conditions sont évalués selon la logique condition 1 ET condition 2 ET ..., la condition de notre exemple ne sera jamais positive dans une seule fonction SOMME.SI.ENS. Nous allons donc faire deux sommes différentes (une avant la période à exclure et une après cette période) et on additionne les deux sommes.

Utiliser SOMME.SI.ENS dans un Tableau Excel

Jusqu'ici, nous avons utilisé dans les formules les plages désignées par leur adresse. Sachez, qu'il est également possible d'utiliser les plages nommées.
Et nous pouvons utiliser la fonction SOMME.SI.ENS (comme tout autre fonction Excel d'ailleurs) dans un "Tableau".
Le comportement des fonctions est le même, il est juste nécessaire de faire attention à la syntaxe des formules. Chaque champ est mentionné comme: NomDeTableau[NomDeColonne]
Dans notre exemple, le tableau s'appelle "Exemple1". La fonction SOMME.SI.ENS aura alors la forme suivante:

=SOMME.SI.ENS(Exemple1[Montant de la vente];Exemple1[Article];"stylo";Exemple1[Type de vente];"En ligne")

et son résultat sera, évidemment, le même que quand on utilise les adresses…

L'avantage de cette notation est qu'elle est facilement lisible et il est facile de se faire l'idée des conditions utilisées, surtout si on ne connaît pas par cœur l'ordre de colonnes des données utilisées

Informations pratiques – pour une utilisation de SOMME.SI.ENS sans accrocs

Avant de terminer, vous trouverez ci-dessous une série de conseils pratiques très utiles que je peux vous donner après des années d'expérience avec cette fonction…

Conclusion de ce tutoriel

C'est ici que se termine notre tutoriel. J'espère que maintenant, la fonction SOMME.SI.ENS n'a plus de secret pour vous. Il s'agit d'une fonction peu connue mais tout de même très puissante qui peut vous permettre de travailler facilement avec vos données.

Pour aller plus loin dans Excel

Voici d'autres articles sur Excel qui pourraient vous intéresser et vous être utiles dans votre travail pratique avec le tableur de Microsoft…

• Liste de toutes les fonctions Excel (déjà plus de 500 fonctions!)
• Tutoriels sur les principales fonctions: SI, NB.SI, RECHERCHEV, RECHERCHEX, TROUVE,…
• Tout sur Excel en ligne – pourquoi se priver! Cette version d'Excel est gratuite
• Maxi tutoriels sur le Tableau Croisé Dynamique Excel & sur le SOLVEUR Excel
• Calcul de la Moyenne pondérée dans Excel
• Calculer le POURCENTAGE sur Excel
• Des Outils Excel Gratuits à télécharger (calendriers, calculateurs,…)

Vous pouvez consulter la page sur le site de Microsoft dédiée à la fonction SOMME.SI.ENS…