Vous devez additionner les ventes d’un commercial, pour une région précise, sur une période donnée ? Avec un seul critère, SOMME.SI suffit. Mais dès que les conditions s’accumulent, une fonction devient vite incontournable : SOMME.SI.ENS.

Cette fonction permet d’additionner uniquement les valeurs qui respectent plusieurs critères en même temps. Elle est particulièrement utile pour analyser un chiffre d’affaires, suivre des dépenses, filtrer des stocks ou construire des tableaux de bord fiables, sans multiplier les colonnes intermédiaires.

Dans cet article, nous allons voir comment fonctionne SOMME.SI.ENS, découvrir sa syntaxe, puis l’utiliser avec des exemples concrets. Vous verrez aussi comment gérer les dates, les critères textuels, les cellules vides et les erreurs les plus fréquentes.

À quoi sert la fonction SOMME.SI.ENS ?

SOMME.SI.ENS additionne une plage de nombres lorsque plusieurs conditions sont vérifiées. Excel applique les critères ligne par ligne : une valeur n’est ajoutée que si elle respecte toutes les conditions indiquées.

Imaginez un tableau de ventes contenant les colonnes suivantes :

  • La date de vente ;
  • Le commercial ;
  • La région ;
  • Le produit ;
  • Le montant.

Vous souhaitez connaître le montant vendu par Julie, dans la région Nord, pour le produit « Clavier ». Une addition classique ne suffit pas. Il faut filtrer les données selon trois critères. C’est exactement le rôle de SOMME.SI.ENS.

La fonction fonctionne selon une logique de type « ET » : le commercial doit être Julie et la région doit être Nord et le produit doit être Clavier. Si une seule condition n’est pas respectée, la ligne n’est pas prise en compte.

La syntaxe de SOMME.SI.ENS

La syntaxe française est la suivante :

=SOMME.SI.ENS(plage_somme; plage_critères1; critère1; [plage_critères2; critère2]; ...)

Chaque argument a un rôle précis :

  • plage_somme : la plage contenant les nombres à additionner ;
  • plage_critères1 : la première plage dans laquelle Excel doit rechercher une condition ;
  • critère1 : la condition à respecter dans cette première plage ;
  • plage_critères2 et critère2 : les éventuelles conditions supplémentaires.

La plage à additionner est toujours placée en premier. C’est une différence importante avec SOMME.SI, dont la structure peut sembler plus intuitive au premier abord. Une petite inversion suffit à obtenir un résultat faux ou une formule difficile à comprendre.

Un premier exemple avec plusieurs critères

Supposons que votre tableau se trouve dans les colonnes suivantes :

  • La colonne A contient les dates ;
  • La colonne B contient les commerciaux ;
  • La colonne C contient les régions ;
  • La colonne D contient les montants.

Pour additionner les ventes de Julie dans la région Nord, utilisez :

=SOMME.SI.ENS(D2:D100;B2:B100;"Julie";C2:C100;"Nord")

Excel examine chaque ligne entre 2 et 100. Si la colonne B contient « Julie » et que la colonne C contient « Nord », le montant correspondant de la colonne D est ajouté.

Pour rendre la formule plus souple, vous pouvez placer les critères dans des cellules. Par exemple, si F2 contient le nom du commercial et G2 la région :

=SOMME.SI.ENS($D$2:$D$100;$B$2:$B$100;F2;$C$2:$C$100;G2)

Cette méthode est très pratique pour créer un petit outil de recherche. Il suffit de modifier F2 ou G2 pour actualiser le résultat, sans toucher à la formule. Les signes dollar permettent de conserver les plages fixes lorsque vous recopiez la formule.

Utiliser des critères numériques

Les critères ne sont pas forcément du texte. SOMME.SI.ENS peut aussi comparer des nombres avec les opérateurs suivants :

  • > supérieur à ;
  • < inférieur à ;
  • >= supérieur ou égal à ;
  • <= inférieur ou égal à ;
  • <> différent de ;
  • = égal à.

Pour additionner les montants supérieurs ou égaux à 1 000 € :

=SOMME.SI.ENS(D2:D100;D2:D100;">=1000")

Le critère est placé entre guillemets, car il contient un opérateur. Sans ces guillemets, Excel risque d’interpréter la formule de manière incorrecte.

Vous pouvez également combiner une condition numérique avec un critère textuel. Pour additionner les ventes de Julie supérieures à 1 000 € :

=SOMME.SI.ENS(D2:D100;B2:B100;"Julie";D2:D100;">=1000")

Il faut toutefois garder à l’esprit que la plage_somme peut aussi être utilisée comme plage de critères. Excel vérifie alors le montant avant de l’ajouter.

Construire un critère à partir d’une cellule

Lorsque l’opérateur est stocké dans une formule et que la valeur vient d’une cellule, il faut utiliser le symbole &.

Si la cellule F2 contient la valeur 1 000, la formule devient :

=SOMME.SI.ENS(D2:D100;D2:D100;">="&F2)

Le texte ">=" est associé au contenu de F2 grâce à la concaténation. Excel construit ainsi le critère >=1000.

Cette technique est essentielle pour les tableaux de bord. Vous pouvez par exemple placer un seuil dans une cellule, le modifier à la volée et voir immédiatement le résultat évoluer. Excel apprécie les formules dynamiques, et votre utilisateur aussi.

Filtrer des dates avec SOMME.SI.ENS

Les dates sont souvent à l’origine de résultats surprenants. Dans Excel, une date est en réalité un nombre formaté. Pour filtrer correctement une période, il est recommandé d’utiliser deux critères : une date de début et une date de fin.

Pour additionner les ventes réalisées entre le 1er janvier 2024 et le 31 janvier 2024 :

=SOMME.SI.ENS(D2:D100;A2:A100;">="&DATE(2024;1;1);A2:A100;"<="&DATE(2024;1;31))

La première condition conserve les dates postérieures ou égales au 1er janvier. La seconde conserve celles antérieures ou égales au 31 janvier.

Une solution plus pratique consiste à placer la date de début en F2 et la date de fin en G2 :

=SOMME.SI.ENS($D$2:$D$100;$A$2:$A$100;">="&$F$2;$A$2:$A$100;"<="&$G$2)

Vérifiez que F2 et G2 sont bien reconnues comme des dates par Excel. Si elles sont stockées comme du texte, la formule risque de ne pas retourner le résultat attendu. Un alignement à gauche dans la cellule ou un format inhabituel peut vous mettre sur la piste.

Si vos données contiennent également des heures, la condition « inférieur ou égal à la date de fin » peut exclure certains enregistrements. Pour inclure toute la journée du 31 janvier, utilisez plutôt une limite strictement inférieure au 1er février :

=SOMME.SI.ENS(D2:D100;A2:A100;">="&DATE(2024;1;1);A2:A100;"<"&DATE(2024;2;1))

Cette approche est souvent plus fiable lorsque les cellules contiennent des horaires.

Rechercher du texte avec des caractères génériques

SOMME.SI.ENS accepte les caractères génériques, très utiles lorsque le texte n’est pas parfaitement identique.

  • * remplace un nombre quelconque de caractères ;
  • ? remplace un seul caractère ;
  • ~ permet de rechercher littéralement un astérisque ou un point d’interrogation.

Pour additionner les ventes de tous les produits contenant le mot « bureau » :

=SOMME.SI.ENS(D2:D100;E2:E100;"*bureau*")

Cette formule trouvera par exemple « Bureau réglable », « Accessoires bureau » ou « Grand bureau ». La recherche n’exige pas que la cellule contienne uniquement le mot recherché.

Pour rechercher les références qui commencent par « PC » :

=SOMME.SI.ENS(D2:D100;E2:E100;"PC*")

Attention aux espaces invisibles et aux fautes de frappe. « Nord » et « Nord » suivi d’un espace peuvent produire des résultats différents dans certaines manipulations. La fonction SUPPRESPACE peut aider à nettoyer les données en amont.

Exclure une valeur ou rechercher les cellules non vides

Pour additionner toutes les ventes sauf celles de la région Sud :

=SOMME.SI.ENS(D2:D100;C2:C100;"<>Sud")

Pour exclure les cellules vides d’une plage :

=SOMME.SI.ENS(D2:D100;B2:B100;"<>")

Et pour sélectionner uniquement les cellules vides :

=SOMME.SI.ENS(D2:D100;B2:B100;"")

Ces critères sont utiles pour contrôler la qualité d’un fichier. Vous pouvez, par exemple, calculer le montant des ventes dont le commercial n’a pas encore été renseigné. Ce n’est pas toujours le chiffre le plus glorieux, mais il permet de repérer rapidement les données à corriger.

Les erreurs fréquentes à éviter

La plupart des problèmes rencontrés avec SOMME.SI.ENS viennent de quelques erreurs simples.

  • Les plages n’ont pas la même taille : D2:D100 doit correspondre à B2:B100 et C2:C100. Évitez de mélanger D2:D100 avec B2:B99.
  • La plage_somme est mal placée : elle doit être le premier argument de la fonction.
  • Les opérateurs ne sont pas entre guillemets : utilisez =1000″ et non ;>=1000.
  • Le séparateur n’est pas le bon : dans Excel français, le point-virgule est généralement utilisé. Selon vos paramètres régionaux, une virgule peut être nécessaire.
  • Les nombres sont stockés comme du texte : une colonne de montants importée depuis un logiciel peut contenir des valeurs non numériques.
  • Les dates sont du texte : vérifiez leur reconnaissance par Excel avant de construire vos critères.
  • Les espaces superflus perturbent les recherches : nettoyez les données avec SUPPRESPACE ou utilisez une table structurée.

Lorsque la formule retourne 0, cela ne signifie pas forcément qu’elle est cassée. Il est possible qu’aucune ligne ne respecte tous les critères. Testez chaque condition séparément afin d’identifier celle qui bloque le résultat.

Associer SOMME.SI.ENS à un tableau Excel

Transformer votre plage en tableau Excel est une excellente habitude. Sélectionnez vos données, utilisez Ctrl + T, puis attribuez un nom au tableau, par exemple Ventes.

La formule devient alors plus lisible :

=SOMME.SI.ENS(Ventes[Montant];Ventes[Commercial];"Julie";Ventes[Région];"Nord")

Les références structurées s’adaptent automatiquement lorsque vous ajoutez de nouvelles lignes. Fini le D2:D100 qui oublie mystérieusement la nouvelle vente ajoutée en ligne 101. Le tableau inclut automatiquement les données supplémentaires.

Les formules sont également plus faciles à relire. Entre Ventes[Montant] et D2:D100, lequel vous semble le plus parlant six mois plus tard ? Votre futur vous remerciera probablement pour ce petit effort.

Quand utiliser une autre fonction ?

SOMME.SI.ENS est idéale lorsque vous devez additionner selon plusieurs critères. Toutefois, certaines situations peuvent nécessiter une autre approche.

  • NB.SI.ENS compte les lignes répondant à plusieurs critères, sans additionner de montants.
  • MOYENNE.SI.ENS calcule une moyenne selon plusieurs conditions.
  • SOMMEPROD permet de gérer des calculs plus complexes, notamment certaines conditions basées sur des formules.
  • Les tableaux croisés dynamiques sont adaptés à l’exploration rapide de grands volumes de données.
  • La fonction FILTRE peut être intéressante dans les versions récentes d’Excel lorsque vous souhaitez afficher les lignes correspondantes avant de les analyser.

Pour la majorité des besoins courants, SOMME.SI.ENS reste néanmoins simple, rapide et suffisamment puissante. Elle évite souvent de créer plusieurs colonnes de calcul uniquement pour obtenir un total filtré.

Une méthode simple pour construire vos formules

Pour éviter les erreurs, construisez votre formule progressivement :

  • Commencez par la plage à additionner ;
  • Ajoutez le premier couple plage de critères/critère ;
  • Vérifiez le résultat ;
  • Ajoutez ensuite les conditions une par une ;
  • Remplacez finalement les valeurs écrites en dur par des références de cellules si nécessaire.

Par exemple, partez de :

=SOMME.SI.ENS(D2:D100;B2:B100;"Julie")

Puis ajoutez la région :

=SOMME.SI.ENS(D2:D100;B2:B100;"Julie";C2:C100;"Nord")

Enfin, ajoutez la période :

=SOMME.SI.ENS(D2:D100;B2:B100;"Julie";C2:C100;"Nord";A2:A100;">="&DATE(2024;1;1);A2:A100;"<"&DATE(2024;2;1))

Cette progression permet de repérer immédiatement le critère qui provoque un résultat inattendu. C’est bien plus efficace que de construire une formule de quinze lignes, puis de la regarder avec l’espoir qu’Excel devine votre intention.

Le réflexe à retenir

SOMME.SI.ENS devient vite un réflexe dès que vous devez répondre à une question du type : « Quel est le total pour telle catégorie, dans telle zone, pendant telle période ? »

Retenez surtout ces trois principes :

  • La plage à additionner se place en premier ;
  • Chaque condition est composée d’une plage et d’un critère ;
  • Toutes les conditions doivent être respectées pour qu’une ligne soit additionnée.

En maîtrisant les opérateurs, les dates, les références de cellules et les caractères génériques, vous pourrez construire des analyses beaucoup plus souples. Et lorsque votre fichier commencera à prendre de l’ampleur, pensez aux tableaux Excel : ils rendent les formules plus lisibles et évitent les plages qui s’arrêtent au mauvais endroit.