Analyser un tableau Excel, c’est souvent commencer par une question très simple : combien ai-je vendu dans telle région, pour tel produit et sur telle période ? Lorsque plusieurs critères entrent en jeu, la fonction SOMME.SI.ENS devient rapidement indispensable.
Elle permet d’additionner uniquement les valeurs qui répondent à plusieurs conditions. Ventes par commercial, dépenses d’un service, chiffre d’affaires mensuel, suivi des stocks… Une fois maîtrisée, elle évite les filtres manuels et les calculs fastidieux.
Dans ce guide, nous allons voir comment fonctionne la somme si ensemble Excel, avec des exemples concrets, des astuces pratiques et quelques pièges à éviter.
À quoi sert la fonction SOMME.SI.ENS ?
La fonction SOMME.SI.ENS additionne des cellules lorsqu’elles respectent plusieurs critères. Elle est particulièrement utile lorsque vous devez croiser différentes informations dans un tableau.
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 « Ordinateur ». Une simple addition ne suffit plus. Il faut filtrer les lignes selon trois conditions. C’est exactement le rôle de SOMME.SI.ENS.
La fonction fonctionne selon une logique très simple : tous les critères doivent être respectés simultanément. Si une ligne ne correspond pas à l’un des critères, elle n’est pas incluse dans le total.
La syntaxe de SOMME.SI.ENS
Voici la syntaxe de la fonction :
=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 valeurs à additionner ;
- plage_critères1 : la plage dans laquelle Excel doit vérifier le premier critère ;
- critère1 : la condition à respecter ;
- plage_critères2 et critère2 : les conditions supplémentaires, facultatives.
La fonction accepte donc deux critères ou davantage. En pratique, vous pouvez en ajouter plusieurs, à condition que les plages utilisées aient toutes la même taille.
Petit point important : dans SOMME.SI.ENS, la plage à additionner est placée en premier. C’est l’inverse de la fonction SOMME.SI. Cette différence paraît anodine, mais elle explique beaucoup d’erreurs de formule.
Un premier exemple avec deux 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 réalisées par Julie dans la région Nord, utilisez la formule suivante :
=SOMME.SI.ENS(D2:D100;B2:B100; »Julie »;C2:C100; »Nord »)
Excel examine chaque ligne. Il conserve uniquement celles où le commercial est Julie et où la région est Nord. Il additionne ensuite les montants correspondants de la colonne D.
Le mot ET est essentiel ici. Avec SOMME.SI.ENS, les critères sont cumulatifs. Une vente réalisée par Julie dans la région Sud ne sera pas retenue. Une vente réalisée par Marc dans la région Nord non plus.
Utiliser des cellules comme critères
Écrire directement « Julie » et « Nord » dans une formule fonctionne, mais ce n’est pas toujours pratique. Si vous souhaitez modifier facilement les critères, placez-les dans des cellules.
Par exemple :
- La cellule F2 contient le nom du commercial ;
- La cellule G2 contient la région.
La formule devient :
=SOMME.SI.ENS(D2:D100;B2:B100;F2;C2:C100;G2)
Il suffit maintenant de remplacer le nom dans F2 ou la région dans G2 pour obtenir un nouveau résultat. Cette méthode est idéale pour créer un petit tableau de bord ou un modèle réutilisable.
Vous pouvez également utiliser des listes déroulantes afin de sélectionner les critères sans les saisir à la main. Cela limite les fautes de frappe et rend le fichier beaucoup plus agréable à utiliser.
Ajouter une condition sur une date
Les dates sont un cas très fréquent avec SOMME.SI.ENS. Pour calculer les ventes réalisées pendant le mois de janvier 2025, vous pouvez utiliser deux critères :
- La date doit être supérieure ou égale au 1er janvier 2025 ;
- La date doit être inférieure ou égale au 31 janvier 2025.
La formule peut s’écrire ainsi :
=SOMME.SI.ENS(D2:D100;A2:A100; »>= »&DATE(2025;1;1);A2:A100; »<="&DATE(2025;1;31))
Les opérateurs de comparaison doivent être placés entre guillemets. Lorsque la date vient d’une cellule ou d’une fonction, on la concatène avec l’opérateur à l’aide du symbole &.
Vous pouvez aussi utiliser des cellules contenant les dates de début et de fin. Si F2 contient la date de début et G2 la date de fin :
=SOMME.SI.ENS(D2:D100;A2:A100; »>= »&F2;A2:A100; »<="&G2)
Cette version est plus flexible. Elle permet de modifier la période sans toucher à la formule.
Une précaution à garder en tête : les cellules de la colonne Date doivent contenir de vraies dates Excel, et non du texte qui ressemble à une date. Si Excel considère « 15/01/2025 » comme du texte, la formule risque de renvoyer un résultat incorrect.
Les opérateurs de comparaison
SOMME.SI.ENS accepte plusieurs opérateurs pour affiner les critères :
- « = » : égal à ;
- « <>« : différent de ;
- « > » : supérieur à ;
- « >= » : supérieur ou égal à ;
- « <" : inférieur à ;
- « <=" : inférieur ou égal à.
Par exemple, pour additionner les montants supérieurs ou égaux à 1 000 € :
=SOMME.SI.ENS(D2:D100;D2:D100; »>=1000″)
Pour additionner les ventes qui ne concernent pas la région Sud :
=SOMME.SI.ENS(D2:D100;C2:C100; »<>Sud »)
Et pour additionner les montants compris entre 500 € et 2 000 € :
=SOMME.SI.ENS(D2:D100;D2:D100; »>=500″;D2:D100; »<=2000")
Dans ce dernier exemple, la même plage apparaît deux fois. Ce n’est pas une erreur : Excel vérifie simplement deux conditions différentes sur les montants.
Rechercher du texte avec les caractères génériques
Les caractères génériques permettent de travailler avec du texte partiel. Les deux principaux sont :
- * : remplace un nombre quelconque de caractères ;
- ? : remplace un seul caractère.
Si vous souhaitez additionner les ventes de tous les produits contenant le mot « pro », utilisez :
=SOMME.SI.ENS(D2:D100;E2:E100; »*pro* »)
Cette formule prendra en compte « Pro », « Professionnel » ou encore « ordinateur pro », selon le contenu de votre tableau.
Pour rechercher les références qui commencent par « CL » :
=SOMME.SI.ENS(D2:D100;E2:E100; »CL* »)
Le caractère ? peut être utile lorsque vous connaissez presque toute une référence. Par exemple, « A?25 » correspondra à « AB25 » ou « AC25 », mais pas à « AAB25 ».
SOMME.SI.ENS avec un tableau Excel
Transformer une plage en tableau Excel est une excellente habitude. Sélectionnez vos données, utilisez le raccourci Ctrl + T, puis donnez un nom au tableau dans l’onglet dédié.
Avec un tableau nommé Ventes, contenant les colonnes Montant, Commercial et Région, la formule devient :
=SOMME.SI.ENS(Ventes[Montant];Ventes[Commercial]; »Julie »;Ventes[Région]; »Nord »)
Cette écriture est souvent plus lisible que des références comme D2:D100. Elle présente aussi un avantage important : lorsque vous ajoutez une nouvelle ligne, le tableau s’agrandit automatiquement et la formule continue de fonctionner.
Pour un fichier destiné à être utilisé pendant plusieurs mois, voire plusieurs années, les tableaux Excel sont vivement recommandés. Ils évitent les plages trop courtes et les mises à jour manuelles.
Créer un résumé par commercial ou par région
SOMME.SI.ENS devient particulièrement puissante lorsqu’elle est associée à une liste de critères.
Supposons que la colonne H contienne la liste des commerciaux. En I2, vous pouvez calculer le total de chaque commercial avec :
=SOMME.SI.ENS($D$2:$D$100;$B$2:$B$100;H2)
Les signes $ bloquent les plages lorsque vous recopiez la formule vers le bas. Le critère H2, lui, reste relatif et devient H3, H4, etc.
Vous obtenez ainsi un récapitulatif automatique :
- Julie : 12 450 € ;
- Marc : 9 870 € ;
- Sophie : 14 230 €.
Cette technique permet de construire rapidement un tableau de synthèse sans créer de formule différente pour chaque personne.
La différence entre SOMME.SI et SOMME.SI.ENS
La fonction SOMME.SI convient lorsque vous ne disposez que d’un seul critère. Par exemple :
=SOMME.SI(C2:C100; »Nord »;D2:D100)
Cette formule additionne les montants de la région Nord.
Dès que vous avez plusieurs conditions, utilisez SOMME.SI.ENS :
=SOMME.SI.ENS(D2:D100;C2:C100; »Nord »;B2:B100; »Julie »)
Il est possible de faire fonctionner SOMME.SI.ENS avec un seul critère, mais cela n’est pas nécessairement utile. Choisissez la fonction la plus simple qui répond à votre besoin. Excel aime la précision, mais il n’exige pas que vous sortiez un marteau-piqueur pour planter une punaise.
Les erreurs les plus fréquentes
Si votre formule renvoie zéro alors que vous attendiez un résultat, plusieurs vérifications s’imposent.
- Les plages n’ont pas la même taille : la plage_somme et les plages de critères doivent avoir le même nombre de lignes.
- Une différence de texte : « Nord » et « nord » sont généralement reconnus de la même manière, mais un espace invisible peut bloquer le résultat.
- Des nombres stockés comme texte : une valeur affichée comme 1 250 peut être enregistrée au format texte.
- Une mauvaise position des arguments : dans SOMME.SI.ENS, la plage à additionner vient toujours en premier.
- Des dates qui ne sont pas de vraies dates : vérifiez le format et la valeur réelle de la cellule.
- Des critères mal écrits : les opérateurs comme >= doivent être placés entre guillemets.
Pour repérer les espaces superflus, la fonction SUPPRESPACE peut vous aider. Si vos données proviennent d’un export logiciel, un nettoyage préalable avec SUPPRESPACE, NETTOYER ou Power Query peut faire gagner beaucoup de temps.
Utiliser SOMME.SI.ENS avec des critères facultatifs
Vous pouvez rendre un critère facultatif grâce à une cellule vide. Par exemple, vous souhaitez filtrer par commercial uniquement si F2 est renseignée :
=SI(F2= » »;SOMME(D2:D100);SOMME.SI.ENS(D2:D100;B2:B100;F2))
Si F2 est vide, Excel additionne toutes les ventes. Si F2 contient un nom, il additionne uniquement les ventes correspondantes.
Pour plusieurs critères facultatifs, la formule peut devenir plus longue. Dans ce cas, un tableau croisé dynamique, des segments ou la fonction FILTRE peuvent parfois être plus adaptés. SOMME.SI.ENS reste néanmoins très pratique pour les indicateurs ciblés et les modèles simples.
Quelques bonnes pratiques pour des formules fiables
- Utilisez des tableaux Excel plutôt que des plages arbitraires lorsque vos données évoluent.
- Placez les critères dans des cellules dédiées pour éviter de modifier les formules.
- Donnez des noms explicites aux colonnes et aux tableaux.
- Vérifiez que les dates et les nombres sont bien stockés dans le bon format.
- Testez chaque critère séparément avant de construire une formule complexe.
- Ajoutez une mise en forme conditionnelle pour repérer les valeurs inhabituelles.
- Documentez les cellules servant de paramètres dans vos fichiers partagés.
Une formule complexe n’est pas forcément une bonne formule. Si votre calcul devient difficile à relire, découpez-le en plusieurs indicateurs intermédiaires ou utilisez un tableau croisé dynamique. L’objectif n’est pas de gagner un concours de formule Excel, mais d’obtenir un fichier fiable et compréhensible.
Un exemple complet de tableau de suivi
Voici une formule qui calcule le chiffre d’affaires réalisé par Julie, dans la région Nord, pour le produit contenant le mot « ordinateur », entre le 1er janvier et le 31 mars 2025 :
=SOMME.SI.ENS(D2:D100;B2:B100; »Julie »;C2:C100; »Nord »;E2:E100; »*ordinateur* »;A2:A100; »>= »&DATE(2025;1;1);A2:A100; »<="&DATE(2025;3;31))
Cette seule formule combine :
- Un critère sur le commercial ;
- Un critère sur la région ;
- Une recherche partielle sur le produit ;
- Une date de début ;
- Une date de fin.
Voilà tout l’intérêt de SOMME.SI.ENS : croiser plusieurs dimensions sans devoir filtrer manuellement le tableau à chaque nouvelle question.
Lorsque vous aurez pris l’habitude de placer vos données dans un tableau structuré et vos critères dans des cellules dédiées, vos analyses deviendront beaucoup plus rapides. La fonction est simple dans son principe, mais suffisamment flexible pour alimenter des reportings, des suivis budgétaires et de véritables tableaux de bord Excel.