Excel: SOMME.SI.ENS – addition avec conditions multiples

La fonction Excel SOMME.SI.ENS permet d'additionner des cellules correspondant à plusieurs critères. Il s'agit d'un dérivé de la fonction Excel plus connue: 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
Fonction Excel SOMME.SI.ENS - argument n°1 Fonction Excel SOMME.SI.ENS - argument n°2 Fonction Excel SOMME.SI.ENS - argument n°3

La valeur renvoyée par la fonction SOMME.SI.ENS

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.

Informations pratiques – pour une utilisation sans accrocs

Comme vous pouvez le constater, vous pouvez utiliser au maximum 127 conditions (concernant 127 plages de données – le plus souvent des colonnes – différentes) mais vous devez en utiliser au moins une. Si vous voulez utiliser uniquement une seule condition, utilisez plutôt la fonction SOMME.SI. La SOMME.SI.ENS n'a de sens qu'à partir de deux conditions à remplir.

Faites particulièrement attention aux arguments contenants les conditions (Critères1, Critères2,…) car dans la majorité des cas d'erreurs, c'est là que le problème se trouve.

Voici des points d'attention pour éviter les soucis et les résultats incorrects:

  • Si plusieurs conditions sont appliquées, c'est la logique ET qui est appliquée, c'est-à-dire la condition 1 ET la condition 2, etc.
  • Chaque plage supplémentaire doit avoir le même nombre de lignes et de colonnes que Plage_somme, mais les plages n'ont pas besoin d'être adjacentes. Si vous indiquez des plages dont la taille ne correspond pas, vous obtiendrez une erreur #VALEUR!.
  • Les chaînes de texte dans les critères doivent être placées entre guillemets (""). En utilisant un opérateur, la condition (même numérique) se comporte comme un texte. Exemples: "pomme", ">100", "num*"
  • Les références de cellules dans les critères ne sont pas entre guillemets! Seul l'opérateur est alors entre guillemets. Exemple "<"&B2
  • Les caractères génériques ? et * peuvent être utilisés dans les critères. Un point d'interrogation (?) correspond à n'importe quel caractère et un astérisque (*) correspond à n'importe quelle séquence de caractères (zéro ou plus). Attention, ces deux caractères ne peuvent être utilisés qu'avec des textes. Vous ne pouvez pas les utiliser ni pour trouver des nombres (ex. "*5" pour trouver 25 ou 35 ne marchera pas) ni pour trouver des dates (ex. "*-1-2022" pour trouver toutes les de janvier 2022 ne marchera pas non plus)
  • Pour trouver un point d'interrogation ou un astérisque, utilisez un tilde (~) devant le point d'interrogation ou l'astérisque (c'est-à-dire ~?, ~*).
  • L'ordre des arguments est différent entre les fonctions SOMME.SI.ENS et SOMME.SI. La plage des valeurs à additionner (Plage_somme) est le premier argument de SOMME.SI.ENS mais le troisième argument de SOMME.SI.

Exemples pratiques d'utilisation de SOMME.SI.ENS

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.

Excel: exemple de listing des ventes

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 €

Excel: SOMME.SI.ENS (exemple basique)

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)

SOMME.SI.ENS (exemple 2)

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

Exemple avec condition numérique

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

Excel: SOMME.SI.ENS dans un Tableau

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…

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

Laissez un commentaire

Votre adresse e-mail ne sera pas publiée.