Fiche
SOMME.SI.ENS et NB.SI.ENS : répondre à « combien ? »
SOMME.SI.ENS et NB.SI.ENS dans Excel : syntaxe, critères de date et de texte, caractères génériques, erreurs qui faussent le total. Exemples concrets.
Mis à jour le
SOMME.SI.ENS additionne les montants qui remplissent plusieurs conditions. NB.SI.ENS compte les lignes qui les remplissent. Ensemble, elles répondent aux questions « combien ? » et « quel total ? ». Par exemple : quel chiffre d'affaires HT en mars dans la région Ouest, ou combien de factures au-dessus de 1 000 €. Le résultat dépend des plages et des critères choisis ; il suit les changements de données lorsque le calcul est actualisé. Pour des consignes prêtes à copier qui font écrire cette formule par une IA, voir Sommer selon plusieurs critères : SOMME.SI.ENS.
Les syntaxes
=SOMME.SI.ENS(somme_plage; plage_critères1; critères1; [plage_critères2; critères2]; …)
=NB.SI.ENS(plage_critères1; critères1; [plage_critères2; critères2]; …)
Points clés, d'après Microsoft :
- dans SOMME.SI.ENS, la plage à additionner vient en premier (dans SOMME.SI, elle vient en troisième) ;
- jusqu'à 127 paires plage et critère ;
- chaque plage de critères a le même nombre de lignes et de colonnes que la plage à additionner (ou que la première plage de critères pour NB.SI.ENS) ;
- dans NB.SI.ENS, un critère qui renvoie à une cellule vide est traité comme la valeur 0.
Écrire les critères
| Ce que vous voulez | Critère à écrire |
|---|---|
| Égal à un texte | "Ouest" |
| Différent d'un texte | "<>Ouest" |
| Supérieur à un nombre | ">1000" |
| Commence par une lettre | "A*" |
| Exactement six caractères finissant par « es » | "????es" |
| Comparé au contenu d'une cellule | ">"&H1 |
Le point d'interrogation remplace un caractère, l'astérisque une suite de caractères. Pour chercher un vrai ? ou *, placez un tilde ~ devant. Les opérateurs (>, <, <>) et les textes se mettent entre guillemets. Microsoft conseille de vérifier les guillemets des critères textuels lorsqu’un zéro inattendu apparaît, sans établir de classement des causes.
Exemples sur un tableau de ventes
Les colonnes de ce tableau illustratif, nommé tbl_Ventes, sont Date, Client, Région, Produit, Montant HT et Payée.
« Quel chiffre d'affaires HT pour la région Ouest ? »
=SOMME.SI.ENS(tbl_Ventes[Montant HT]; tbl_Ventes[Région]; "Ouest")
« Quel chiffre d'affaires HT en mars 2026 ? » (01/03/2026 en H1, 01/04/2026 en H2, borne de fin exclue)
=SOMME.SI.ENS(tbl_Ventes[Montant HT]; tbl_Ventes[Date]; ">="&H1; tbl_Ventes[Date]; "<"&H2)
Microsoft montre ce principe dans un exemple de NB.SI.ENS, avec "<"&B4. L'opérateur entre guillemets se colle à la référence de cellule par &. Utilisez de vraies dates Excel dans ces cellules, pas du texte qui ressemble à une date. La borne de fin exclue inclut les heures du 31/03 sans inclure le 01/04.
« Combien de lignes non payées au-dessus de 1 000 € HT ? »
=NB.SI.ENS(tbl_Ventes[Payée]; "Non"; tbl_Ventes[Montant HT]; ">1000")
« Combien de lignes pour les clients dont le nom commence par "Martin" ? »
=NB.SI.ENS(tbl_Ventes[Client]; "Martin*")
Pour une question comme « total éligible », il faut d’abord retrouver la définition des montants retenus. Les fonctions documentaires d’ALMA permettent de créer un agent alimenté par votre note de calcul. Testez le traitement d’un avoir et contrôlez la règle citée. Traduisez ensuite cette règle dans les critères Excel : une réponse documentaire ne certifie pas la somme des lignes.
Ce qui fausse le résultat
| Symptôme | Cause à examiner | Correction |
|---|---|---|
| Résultat 0 alors que des lignes correspondent | Critère texte sans guillemets | Ajouter les guillemets |
| Un client dont une partie des lignes manque | Espaces en trop ou guillemets différents dans les noms | Nettoyer avec SUPPRESPACE ou EPURAGE (conseil de Microsoft, page NB.SI) |
| Total trop faible sur une période | Une partie des dates est en texte | Convertir les dates : voir Préparer un classeur Excel |
| Total étrange | La colonne à additionner contient des VRAI/FAUX, comptés 1 et 0 selon Microsoft | Vérifier le contenu de la colonne |
| Formule refusée ou résultat incohérent | Plages de tailles différentes | Utiliser des colonnes entières du même tableau |
À savoir aussi : d'après Microsoft, NB.SI ne tient pas compte de la casse. « ouest » et « OUEST » sont comptés ensemble, ce qui arrange souvent, mais pas toujours. NB.SI renvoie aussi des résultats faux sur des textes de plus de 255 caractères.
Distinguer ET, OU et commandes distinctes
Des plages de même taille peuvent rester mal alignées. Dans un exemple fictif, B2 contient Ouest et D2 vaut 100 ; B3 contient Est et D3 vaut 200 ; B4 contient Ouest. =SOMME.SI.ENS(D2:D3;B3:B4;"Ouest") associe les montants aux régions de la ligne suivante et retient 200, alors que la somme attendue sur les lignes 2 et 3 est 100. Vérifiez donc les premières et dernières lignes de chaque plage, pas seulement leurs dimensions. Les colonnes du même tableau structuré réduisent ce risque de décalage.
Les critères d’une même formule sont cumulatifs : Région = Ouest et Montant > 1 000. Additionner deux comptages peut en revanche compter deux fois une ligne qui appartient aux deux groupes. Exemple fictif : sur cinq ventes, trois sont à l’Ouest, deux dépassent 1 000 €, et une remplit les deux conditions. La réunion des groupes comporte quatre lignes, pas cinq.
NB.SI.ENS ne regroupe pas les articles d’une facture. Deux lignes de 600 € et 500 € appartiennent à une facture de 1 100 € : aucune ligne ne dépasse 1 000 €, mais la facture dépasse ce seuil. Regroupez d’abord les montants par identifiant de facture pour répondre à cette autre question.
Contrôler un résultat
Une formule exacte peut répondre à la mauvaise question. Trois contrôles simples :
- Le total sans critère :
=SOMME(tbl_Ventes[Montant HT])doit être égal à la somme des résultats par région, si chaque ligne a une région. - Le comptage associé : à côté d'un total, affichez le NB.SI.ENS avec les mêmes critères. Vous savez alors sur combien de lignes porte le chiffre.
- Un cas connu : vérifiez la formule sur un client dont vous connaissez le total.
Ces formules servent aussi à vérifier une réponse de Copilot : voir Copilot dans Excel est-il fiable ?. Pour une répartition sur beaucoup de valeurs (tous les clients, tous les mois), le tableau croisé dynamique évite d'écrire une formule par ligne.
Questions fréquentes
Pourquoi SOMME.SI.ENS renvoie 0 ?
Vérifiez les critères, leurs guillemets, les dates, les montants numériques et les espaces. Un zéro ne suffit pas à identifier la cause ; il peut aussi être le résultat attendu.
Combien de critères peut-on mettre dans SOMME.SI.ENS ?
Microsoft indique jusqu'à 127 paires plage et critère, pour SOMME.SI.ENS comme pour NB.SI.ENS.
Quelle différence entre SOMME.SI et SOMME.SI.ENS ?
SOMME.SI accepte un critère, SOMME.SI.ENS plusieurs. La plage à additionner vient en premier dans SOMME.SI.ENS et en troisième dans SOMME.SI. Microsoft conseille d'y prendre garde en copiant une formule.
Sources
Pages consultées pour écrire cet article :
- https://alma.iarche.fr/fr/features
- https://support.microsoft.com/fr-fr/office/fonction-somme-si-ens-c9e748f5-7ea7-455d-9406-611cebce642b
- https://support.microsoft.com/fr-fr/office/fonction-nb-si-ens-dda3dc6e-f74e-4aee-88bc-aa8c2a866842
- https://support.microsoft.com/fr-fr/office/nb-si-nb-si-fonction-e0de10c6-f885-4e71-abb4-1f464816df34