Vous devez additionner les ventes d’un commercial dans une région précise, uniquement pour un mois donné ? La fonction SOMME.SI.ENS est faite pour ça. Elle permet d’additionner des valeurs en appliquant plusieurs critères simultanément.
Très pratique dans un tableau de suivi, un budget, un reporting commercial ou un fichier de gestion, elle évite les additions manuelles et les filtres répétitifs. Dans ce guide, nous allons voir comment fonctionne la formule SOMME.SI.ENS, avec des exemples concrets et quelques astuces pour éviter les erreurs classiques.
À quoi sert la fonction SOMME.SI.ENS ?
La fonction SOMME.SI.ENS additionne les cellules d’une plage lorsque plusieurs conditions sont respectées. Excel vérifie chaque ligne et ne prend en compte que celles qui correspondent à tous les critères indiqués.
Imaginez un tableau contenant les colonnes suivantes :
- La date de vente ;
- Le commercial ;
- La région ;
- Le produit ;
- Le montant de la vente.
Vous pouvez alors demander à Excel :
- d’additionner les ventes réalisées par Julie ;
- dans la région Nord ;
- pour le produit « Ordinateur » ;
- entre le 1er janvier et le 31 janvier.
Une seule formule suffit. Pas besoin de faire quatre calculs séparés ni de jongler avec les filtres.
La syntaxe de SOMME.SI.ENS
La syntaxe française est la suivante :
=SOMME.SI.ENS(plage_somme ; plage_critères1 ; critères1 ; [plage_critères2 ; critères2] ; …)
Chaque argument a un rôle précis :
- plage_somme : la plage contenant les valeurs à additionner ;
- plage_critères1 : la première plage dans laquelle Excel doit rechercher une condition ;
- critères1 : la condition à respecter dans cette première plage ;
- plage_critères2 et critères2 : les conditions supplémentaires, facultatives.
La fonction accepte plusieurs couples « plage de critères / critère ». Les conditions sont cumulatives : une ligne doit répondre à toutes les conditions pour être incluse dans le total.
Attention à un détail important : dans SOMME.SI.ENS, la plage à additionner apparaît en premier. C’est différent de la fonction SOMME.SI, dont l’ordre des arguments peut prêter à confusion.
Un premier exemple simple
Supposons que votre tableau occupe les cellules A1:E10 :
- La colonne A contient les dates ;
- La colonne B contient les commerciaux ;
- La colonne C contient les régions ;
- La colonne D contient les produits ;
- La colonne E contient les montants.
Pour additionner toutes les ventes réalisées par « Julie », utilisez :
=SOMME.SI.ENS(E2:E10 ; B2:B10 ; « Julie »)
Excel parcourt la colonne B. Lorsqu’il trouve « Julie », il additionne la valeur correspondante dans la colonne E.
Pour limiter le calcul à la région « Nord », ajoutez un deuxième critère :
=SOMME.SI.ENS(E2:E10 ; B2:B10 ; « Julie » ; C2:C10 ; « Nord »)
Cette formule additionne uniquement les montants pour lesquels le commercial est Julie et la région est Nord. Une ligne correspondant à Julie mais située dans la région Sud ne sera pas prise en compte.
Utiliser une cellule comme critère
Écrire directement les critères dans la formule fonctionne, mais ce n’est pas toujours très souple. Il est souvent préférable de placer les critères dans des cellules dédiées.
Par exemple :
- G2 contient le nom du commercial ;
- G3 contient la région ;
- G4 contiendra éventuellement le produit.
La formule devient :
=SOMME.SI.ENS(E2:E10 ; B2:B10 ; G2 ; C2:C10 ; G3)
Il suffit maintenant de modifier G2 ou G3 pour obtenir un autre résultat. Le tableau de bord devient beaucoup plus interactif, sans avoir à modifier la formule à chaque fois.
Pour conserver les références lorsque vous recopiez la formule, utilisez les références absolues :
=SOMME.SI.ENS($E$2:$E$10 ; $B$2:$B$10 ; G2 ; $C$2:$C$10 ; G3)
Les plages restent fixes, tandis que les cellules de critères peuvent évoluer selon la position de la formule.
Ajouter un critère numérique
Les critères ne sont pas limités au texte. Vous pouvez aussi appliquer des conditions numériques comme « supérieur à », « inférieur ou égal à » ou « différent de ».
Pour additionner les ventes supérieures à 1 000 € :
=SOMME.SI.ENS(E2:E10 ; E2:E10 ; « >1000 »)
Pour additionner les ventes comprises entre 500 € et 2 000 € :
=SOMME.SI.ENS(E2:E10 ; E2:E10 ; « >=500 » ; E2:E10 ; « <=2000 »)
Vous pouvez également combiner une condition numérique avec un critère textuel :
=SOMME.SI.ENS(E2:E10 ; B2:B10 ; « Julie » ; E2:E10 ; « >1000 »)
Cette formule additionne uniquement les ventes de Julie dont le montant dépasse 1 000 €.
Construire un critère avec une cellule
Lorsque l’opérateur est écrit directement dans la formule, il doit être placé entre guillemets. En revanche, si la valeur se trouve dans une cellule, il faut assembler l’opérateur et la référence avec le symbole &.
Si G5 contient le montant minimal, utilisez :
=SOMME.SI.ENS(E2:E10 ; E2:E10 ; « > »&G5)
Si G5 contient 1 000, Excel interprète correctement le critère « supérieur à 1 000 ».
Pour une borne minimale et une borne maximale placées en G5 et G6 :
=SOMME.SI.ENS(E2:E10 ; E2:E10 ; « >= »&G5 ; E2:E10 ; « <= »&G6)
C’est une technique particulièrement utile dans les tableaux de bord où l’utilisateur choisit lui-même les limites de recherche.
Filtrer les ventes entre deux dates
Les dates sont des nombres pour Excel, même si elles sont affichées sous la forme 15/03/2024. Vous pouvez donc les utiliser avec des opérateurs de comparaison.
Pour additionner les ventes réalisées entre le 1er janvier 2024 et le 31 janvier 2024 :
=SOMME.SI.ENS(E2:E10 ; A2:A10 ; « >= »&DATE(2024;1;1) ; A2:A10 ; « <= »&DATE(2024;1;31))
La fonction DATE rend la formule plus fiable qu’une date écrite directement entre guillemets. Elle évite notamment les problèmes liés au format régional ou à l’interprétation des dates.
Vous pouvez aussi placer les dates dans des cellules. Si G2 contient la date de début et G3 la date de fin :
=SOMME.SI.ENS(E2:E10 ; A2:A10 ; « >= »&G2 ; A2:A10 ; « <= »&G3)
Pour filtrer tout un mois, une méthode pratique consiste à utiliser une borne inférieure et une borne strictement inférieure au premier jour du mois suivant :
=SOMME.SI.ENS(E2:E10 ; A2:A10 ; « >= »&DATE(2024;1;1) ; A2:A10 ; « <« &DATE(2024;2;1))
Cette approche fonctionne aussi lorsque les cellules contiennent des heures. Une date comme 31/01/2024 15:30 est bien inférieure au 1er février 2024, alors qu’elle pourrait être exclue par une condition « inférieur ou égal au 31 janvier » selon la précision des données.
Rechercher un texte partiel avec les caractères génériques
Vous ne connaissez pas toujours le contenu exact d’une cellule. Les caractères génériques permettent alors de rechercher une partie du texte.
- * remplace une suite de caractères ;
- ? remplace un seul caractère ;
- ~ permet de rechercher réellement un astérisque ou un point d’interrogation.
Pour additionner les ventes de tous les produits contenant le mot « bureau » :
=SOMME.SI.ENS(E2:E10 ; D2:D10 ; « *bureau* »)
Cette formule trouvera par exemple « Bureau professionnel », « Accessoires bureau » ou « Petit bureau ».
Pour rechercher les noms commençant par « Dur » :
=SOMME.SI.ENS(E2:E10 ; B2:B10 ; « Dur* »)
Cette technique est très pratique pour analyser des familles de produits ou des catégories dont les intitulés ne sont pas parfaitement homogènes.
Gérer une condition « ou »
Par défaut, SOMME.SI.ENS applique une logique « et ». Toutes les conditions doivent être respectées. Mais que faire si vous souhaitez additionner les ventes de la région Nord ou de la région Sud ?
La solution la plus simple consiste à additionner deux fonctions :
=SOMME.SI.ENS(E2:E10 ; C2:C10 ; « Nord ») + SOMME.SI.ENS(E2:E10 ; C2:C10 ; « Sud »)
Vous pouvez aussi utiliser une plage de critères avec SOMME :
=SOMME(SOMME.SI.ENS(E2:E10 ; C2:C10 ; {« Nord ». »Sud »}))
Selon votre version d’Excel et vos paramètres régionaux, le séparateur des constantes peut varier. Si cette formule ne fonctionne pas, la première méthode reste la plus lisible et la plus facile à maintenir.
Utiliser un tableau structuré
Transformer vos données en tableau Excel est une excellente habitude. Sélectionnez votre plage, utilisez Ctrl + T, puis attribuez un nom au tableau, par exemple Ventes.
La formule peut alors s’écrire ainsi :
=SOMME.SI.ENS(Ventes[Montant] ; Ventes[Commercial] ; G2 ; Ventes[Région] ; G3)
Les références structurées sont plus lisibles que des plages comme E2:E5000. Surtout, le tableau s’étend automatiquement lorsqu’une nouvelle ligne est ajoutée. Votre formule reste donc à jour sans modification manuelle.
Une formule comme celle-ci explique presque toute seule ce qu’elle fait. Même six mois plus tard, vous comprendrez rapidement le calcul. Et c’est un vrai gain de temps lorsque le fichier commence à prendre de l’ampleur.
Les erreurs les plus fréquentes
Une formule SOMME.SI.ENS peut renvoyer un résultat inattendu pour plusieurs raisons.
- Les plages n’ont pas la même taille : la plage_somme et les plages de critères doivent couvrir le même nombre de lignes.
- Les nombres sont stockés comme du texte : une valeur « 1 000 » enregistrée comme texte ne sera pas toujours traitée comme un nombre.
- Les dates sont du texte : vérifiez qu’Excel reconnaît bien vos dates et qu’elles sont alignées à droite dans les cellules avec le format standard.
- Les espaces invisibles : « Nord » et « Nord » suivi d’un espace ne sont pas toujours considérés comme identiques. La fonction SUPPRESPACE peut être utile.
- Les critères textuels manquent de guillemets : écrivez « Julie » dans la formule, sauf si le critère vient d’une cellule.
- Les opérateurs sont mal assemblés : avec une cellule, utilisez « > »&G5 et non « >G5 ».
Si le résultat est égal à zéro alors que des lignes semblent correspondre, commencez par tester chaque critère séparément. Cette méthode permet de repérer rapidement la condition qui bloque le calcul.
Différence entre SOMME.SI et SOMME.SI.ENS
SOMME.SI est adaptée lorsqu’un seul critère suffit :
=SOMME.SI(C2:C10 ; « Nord » ; E2:E10)
SOMME.SI.ENS devient nécessaire dès que vous devez appliquer plusieurs conditions :
=SOMME.SI.ENS(E2:E10 ; C2:C10 ; « Nord » ; B2:B10 ; « Julie »)
Si vous utilisez déjà SOMME.SI avec plusieurs calculs additionnés, il est souvent possible de simplifier votre fichier avec SOMME.SI.ENS. Votre classeur gagne en lisibilité et le risque d’oublier un critère diminue.
Une formule complète pour un tableau de suivi
Imaginons un tableau de ventes structuré nommé Ventes, avec les colonnes Date, Commercial, Région, Produit et Montant. Les cellules G2 à G5 contiennent respectivement :
- G2 : le commercial sélectionné ;
- G3 : la région sélectionnée ;
- G4 : la date de début ;
- G5 : la date de fin.
Pour calculer le total correspondant à tous ces critères :
=SOMME.SI.ENS(Ventes[Montant] ; Ventes[Commercial] ; G2 ; Ventes[Région] ; G3 ; Ventes[Date] ; « >= »&G4 ; Ventes[Date] ; « <« &G5+1)
L’ajout de 1 à la date de fin permet d’inclure toute la journée lorsque les données comportent des heures. Cette formule constitue une excellente base pour un tableau de bord commercial ou financier.
Vous pouvez également prévoir une option « Tous » dans vos cellules de sélection. Dans ce cas, une formule plus avancée peut neutraliser le critère lorsque la cellule contient « Tous » :
=SOMME.SI.ENS(Ventes[Montant] ; Ventes[Commercial] ; SI(G2= »Tous »; »* »;G2) ; Ventes[Région] ; SI(G3= »Tous »; »* »;G3))
Cette méthode convient surtout aux colonnes textuelles. Pour les dates et les nombres, il est souvent plus propre d’utiliser une formule adaptée ou de construire le tableau de bord avec des segments et un tableau croisé dynamique.
Les bons réflexes à retenir
- Commencez toujours par identifier clairement la plage à additionner.
- Ajoutez les critères un par un pour vérifier le résultat.
- Utilisez des cellules de sélection plutôt que de modifier la formule à chaque recherche.
- Employez DATE pour construire des critères fiables sur les périodes.
- Transformez les données en tableau avec Ctrl + T pour bénéficier des références structurées.
- Vérifiez la taille des plages et le type réel des données en cas de résultat surprenant.
Avec SOMME.SI.ENS, vous disposez d’un outil solide pour analyser rapidement vos données sans passer par des calculs compliqués. Une fois les critères bien définis, Excel fait le tri pour vous. Et avouons-le : laisser Excel additionner pendant que vous vous concentrez sur l’analyse, c’est plutôt une bonne répartition des tâches.