Vous souhaitez additionner uniquement les ventes d’un commercial sur une période donnée, ou calculer le chiffre d’affaires réalisé dans une région pour un produit précis ? Avec une seule condition, la fonction SOMME.SI suffit. Mais dès qu’il faut croiser deux critères, Excel propose une formule plus adaptée : SOMME.SI.ENS.
Cette fonction Excel permet d’additionner des valeurs uniquement lorsque plusieurs conditions sont respectées en même temps. C’est l’outil idéal pour analyser un tableau Excel de ventes, filtrer des dépenses ou construire un modèle de suivi fiable sans passer par des calculs manuels.
SOMME.SI.ENS ou SOMME.SI avec deux conditions ?
La première précision est importante : Excel ne possède pas une fonction nommée SOMME.SI avec deux conditions. La fonction prévue pour gérer plusieurs critères est SOMME.SI.ENS.
En version française, sa syntaxe est la suivante :
=SOMME.SI.ENS(plage_somme ; plage_critères1 ; critère1 ; plage_critères2 ; critère2)
Chaque élément joue un rôle précis :
- plage_somme correspond aux cellules à additionner.
- plage_critères1 contient les données examinées par la première condition.
- critère1 indique la première condition à respecter.
- plage_critères2 contient les données examinées par la deuxième condition.
- critère2 indique la deuxième condition à respecter.
Excel additionne une ligne uniquement si les deux conditions sont vraies. C’est le principe logique du ET : la ligne doit satisfaire le critère 1 et le critère 2. Si vous ajoutez un troisième couple plage/critère, la ligne devra également respecter cette troisième condition.
Exemple pratique avec un tableau de ventes
Imaginons un tableau Excel contenant les ventes suivantes :
- Colonne A : date de vente
- Colonne B : commercial
- Colonne C : région
- Colonne D : produit
- Colonne E : montant
Les données peuvent être organisées ainsi :
- 02/01/2025 – Alice – Nord – Ordinateur – 1 200 €
- 04/01/2025 – Benoît – Sud – Écran – 450 €
- 08/01/2025 – Alice – Nord – Écran – 650 €
- 12/01/2025 – Alice – Sud – Ordinateur – 1 100 €
- 15/01/2025 – Benoît – Nord – Ordinateur – 1 350 €
Vous souhaitez calculer le montant total des ventes réalisées par Alice dans la région Nord.
La formule Excel sera :
=SOMME.SI.ENS(E2:E6 ; B2:B6 ; « Alice » ; C2:C6 ; « Nord »)
Excel examine chaque ligne du tableau :
- La ligne 2 correspond à Alice et à la région Nord : elle est ajoutée.
- La ligne 3 concerne Benoît : elle est ignorée.
- La ligne 4 correspond à Alice et à la région Nord : elle est ajoutée.
- La ligne 5 concerne Alice, mais la région est Sud : elle est ignorée.
- La ligne 6 concerne Benoît : elle est ignorée.
Le résultat est donc de 1 850 €. Une formule courte, mais une analyse qui aurait été beaucoup moins agréable à réaliser à la main dès que le tableau comporte plusieurs centaines de lignes.
Utiliser des cellules comme critères
Écrire directement les critères dans la formule fonctionne, mais ce n’est pas toujours la solution la plus pratique. Pour créer un tableau de bord ou un modèle Excel réutilisable, il est préférable de placer les critères dans des cellules.
Par exemple, inscrivez le commercial recherché en G2 et la région en G3. La formule devient :
=SOMME.SI.ENS(E2:E6 ; B2:B6 ; G2 ; C2:C6 ; G3)
Si vous remplacez « Alice » par « Benoît » dans la cellule G2, le résultat se met automatiquement à jour. Même principe pour la région. Cette méthode est particulièrement utile dans un modèle gratuit Excel destiné à être utilisé plusieurs fois.
Elle évite aussi de modifier la formule à chaque nouvelle recherche. Vous changez les cellules de sélection, et Excel fait le reste. C’est une petite étape vers l’automatisation Excel, sans écrire une seule ligne de VBA.
Ajouter une condition numérique
Les critères ne se limitent pas au texte. Vous pouvez demander à Excel d’additionner les montants supérieurs à une certaine valeur, inférieurs à un seuil ou compris entre deux limites.
Pour additionner les ventes d’Alice dans la région Nord dont le montant est supérieur à 1 000 €, utilisez trois conditions :
=SOMME.SI.ENS(E2:E6 ; B2:B6 ; « Alice » ; C2:C6 ; « Nord » ; E2:E6 ; « >1000 »)
Le signe supérieur est placé entre guillemets avec la valeur. Les opérateurs les plus courants sont :
- « >1000 » pour les montants supérieurs à 1 000.
- « >=1000 » pour les montants supérieurs ou égaux à 1 000.
- « <500 » pour les montants inférieurs à 500.
- « <>0 » pour exclure les valeurs égales à zéro.
- « =650 » pour rechercher exactement le montant 650.
Si le seuil se trouve en cellule G4, il faut assembler l’opérateur et la référence avec le symbole & :
=SOMME.SI.ENS(E2:E6 ; B2:B6 ; G2 ; C2:C6 ; G3 ; E2:E6 ; « > »&G4)
Cette syntaxe est essentielle. Écrire « >G4 » chercherait le texte « >G4 », et non les nombres supérieurs à la valeur contenue dans G4.
Faire une SOMME.SI.ENS entre deux dates
Le filtrage par période est l’un des usages les plus fréquents de la fonction. Pour calculer le total des ventes réalisées entre le 1er janvier et le 31 janvier 2025, vous pouvez utiliser deux critères sur la même colonne de dates.
Si les dates sont en A2:A6 et les montants en E2:E6, la formule est :
=SOMME.SI.ENS(E2:E6 ; A2:A6 ; « >= »&DATE(2025 ; 1 ; 1) ; A2:A6 ; « <= »&DATE(2025 ; 1 ; 31))
Cette formule additionne les valeurs dont la date est supérieure ou égale au 1er janvier et inférieure ou égale au 31 janvier.
Vous pouvez également placer la date de début en G5 et la date de fin en G6 :
=SOMME.SI.ENS(E2:E6 ; A2:A6 ; « >= »&G5 ; A2:A6 ; « <= »&G6)
Cette méthode convient parfaitement à un suivi mensuel. Pour éviter les problèmes liés aux heures enregistrées dans les cellules, une autre approche consiste parfois à utiliser une borne strictement inférieure au premier jour du mois suivant :
=SOMME.SI.ENS(E2:E6 ; A2:A6 ; « >= »&G5 ; A2:A6 ; « <« &G6+1)
Cette variante inclut toutes les ventes du dernier jour, même si les cellules contiennent une heure en plus de la date.
Critères texte et caractères génériques
La fonction accepte également des recherches partielles grâce aux caractères génériques Excel.
- * 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 montants correspondant à tous les produits dont le nom commence par « Ord », utilisez :
=SOMME.SI.ENS(E2:E6 ; D2:D6 ; « Ord* »)
La formule prend ainsi en compte « Ordinateur », « Ordinateur portable » ou toute autre désignation commençant par ces lettres.
Pour rechercher un produit contenant le mot « écran », vous pouvez écrire :
=SOMME.SI.ENS(E2:E6 ; D2:D6 ; « *écran* »)
Attention toutefois aux espaces superflus et aux différences d’orthographe. Une cellule contenant « Écran » avec un espace final peut perturber vos analyses, surtout si les données proviennent d’un import.
La différence entre ET et OU
SOMME.SI.ENS applique naturellement une logique ET. Pour être comptabilisée, une ligne doit respecter toutes les conditions.
Mais comment additionner les ventes réalisées par Alice ou par Benoît ? Dans ce cas, il faut additionner deux fonctions :
=SOMME.SI.ENS(E2:E6 ; B2:B6 ; « Alice ») + SOMME.SI.ENS(E2:E6 ; B2:B6 ; « Benoît »)
Vous pouvez aussi utiliser une liste de critères avec certaines fonctions Excel récentes, mais l’addition de deux formules reste souvent plus lisible et compatible avec d’anciennes versions d’Excel.
Pour combiner un « OU » avec un autre critère, par exemple Alice ou Benoît dans la région Nord, additionnez les deux calculs :
=SOMME.SI.ENS(E2:E6 ; B2:B6 ; « Alice » ; C2:C6 ; « Nord ») + SOMME.SI.ENS(E2:E6 ; B2:B6 ; « Benoît » ; C2:C6 ; « Nord »)
Utiliser un tableau Excel structuré
Lorsque vos données sont converties en tableau Excel avec le raccourci Ctrl + T, les formules deviennent plus lisibles. Supposons que le tableau soit nommé Ventes, avec les colonnes « Commercial », « Région » et « Montant ».
La formule équivalente s’écrit ainsi :
=SOMME.SI.ENS(Ventes[Montant] ; Ventes[Commercial] ; « Alice » ; Ventes[Région] ; « Nord »)
Le tableau s’étend automatiquement lorsque vous ajoutez une nouvelle ligne. Les références restent donc plus solides qu’une plage classique comme E2:E6, qui pourrait oublier les nouvelles données.
Autre avantage : les en-têtes rendent la formule compréhensible au premier coup d’œil. Même plusieurs semaines après sa création, vous saurez immédiatement ce qu’elle calcule.
Les erreurs fréquentes à éviter
Une formule SOMME.SI.ENS peut renvoyer un résultat inattendu pour plusieurs raisons. Voici les vérifications les plus utiles :
- Les plages n’ont pas la même taille. La plage à additionner 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 200 € » importée comme texte ne sera pas additionnée correctement.
- Les dates sont également du texte. Vérifiez leur alignement et leur format avant d’utiliser des critères de période.
- Les critères avec opérateur sont mal écrits. Utilisez « > »&G4 lorsque le seuil est dans une cellule.
- Les espaces invisibles faussent la recherche. Les fonctions SUPPRESPACE ou NETTOYER peuvent aider à nettoyer les données.
- Les séparateurs diffèrent selon la configuration. Dans Excel français, le point-virgule est généralement utilisé entre les arguments.
Si aucune ligne ne respecte les conditions, Excel renvoie généralement 0. Ce résultat ne signifie pas nécessairement que la formule est incorrecte : il peut simplement indiquer qu’aucune donnée ne correspond aux critères.
Quand préférer SOMMEPROD ?
Dans la majorité des cas, SOMME.SI.ENS est la fonction la plus claire et la plus facile à maintenir. Toutefois, SOMMEPROD peut être intéressante pour des critères plus complexes, notamment lorsque vous devez effectuer des calculs conditionnels particuliers.
Pour reprendre l’exemple d’Alice dans la région Nord, une formule SOMMEPROD pourrait ressembler à ceci :
=SOMMEPROD((B2:B6= »Alice »)*(C2:C6= »Nord »)*E2:E6)
Chaque condition produit une série de VRAI/FAUX convertie en 1 ou 0. Les lignes qui respectent les deux critères conservent leur montant, tandis que les autres sont neutralisées.
Cette solution est puissante, mais elle est généralement moins lisible. Commencez donc par SOMME.SI.ENS, puis utilisez SOMMEPROD lorsque la situation le justifie.
Une formule à retenir
Pour additionner une plage selon deux conditions, retenez ce modèle :
=SOMME.SI.ENS(plage_somme ; plage_critère1 ; critère1 ; plage_critère2 ; critère2)
Par exemple :
=SOMME.SI.ENS(E:E ; B:B ; « Alice » ; C:C ; « Nord »)
La fonction s’adapte ensuite à presque tous les suivis : dépenses par catégorie et par mois, ventes par commercial et par région, heures travaillées par salarié et par projet, ou encore résultats supérieurs à un seuil.
Une fois la logique comprise, deux conditions ne sont plus un obstacle. Et si votre tableau évolue régulièrement, pensez à le convertir en tableau structuré : votre formule Excel sera plus fiable, plus lisible et prête à accompagner vos prochaines lignes de données.