Qu'est-ce que la fonction NB.
NB.SI.ENS te sert dès que NB.SI ne suffit plus parce que tu dois croiser plusieurs filtres à la fois, plutôt que de filtrer ton tableau à la main ou d'empiler plusieurs NB.SI que tu devrais ensuite recouper toi-même.
Concrètement, c'est elle qui compte les commerciaux du département Nord ayant dépassé leur objectif, les commandes expédiées en janvier, ou les transactions comprises entre deux montants. Une ligne n'est comptée que si toutes tes conditions sont remplies en même temps.
Comprendre chaque paramètre de la fonction NB.SI.ENS
plage1
: la première plage à évaluerElle définit les cellules qu'Excel va tester contre critère1. Tu peux utiliser une référence directe comme A2:A100, une colonne entière comme A:A, ou un tableau structuré.
Toutes les plages passées à NB.SI.ENS doivent avoir exactement la même dimension : même nombre de lignes et de colonnes.
critère1
: la condition que les cellules de `plage1` doivent respecter pour être comptéesTu peux utiliser du texte ("Paris"), un nombre (1000), un opérateur de comparaison entre guillemets (">1000"), ou des caractères génériques ("*formation*").
Pour combiner un opérateur et une valeur de cellule, concatène avec & : ">="&B1. L'opérateur reste entre guillemets, la référence en dehors.
[plage2; critère2; ...]
: paires supplémentaires plage/critère(facultatif)Chaque paire ajoute une condition ET : une ligne n'est comptée que si elle satisfait simultanément toutes les conditions. Tu peux en ajouter jusqu'à 127 paires, soit 127 conditions croisées.
Les plages supplémentaires n'ont pas besoin d'être adjacentes à plage1, mais elles doivent avoir exactement le même nombre de lignes.
Attention : NB.SI.ENS applique un ET logique entre toutes les conditions. Pour un OU (compter les lignes qui remplissent AU MOINS une condition), additionne plusieurs NB.SI ou NB.SI.ENS séparés et soustrais les doublons si nécessaire.
Exemples pratiques pas à pas
Comment croiser deux critères avec la fonction NB.SI.ENS
Le point d'activité de lundi arrive et on va te demander si le Premium décolle vraiment dans le Nord. Tes ventes sont listées une par une avec leur région, leur produit, le commercial qui a signé et le montant, et tu veux le nombre de ventes Premium réalisées dans le Nord sans filtrer ton tableau ni compter à la main. La fonction NB.SI.ENS croise les deux conditions et te sort ce compte d'un coup.
- 1Dans une cellule, écris
=NB.SI.ENS(. - 2En 1er argument : sélectionne la colonne des régions,
A2:A19. C'est sur elle que porte ta première condition. - 3En 2ᵉ argument : écris le critère correspondant,
"Nord". À ce stade, seules les ventes de cette région restent en lice. - 4En 3ᵉ argument : sélectionne la colonne des produits,
B2:B19. Elle doit couvrir exactement les mêmes lignes que la première, sinon la fonction renvoie l'erreur#VALEUR!. - 5En 4ᵉ argument : écris le second critère,
"Premium". Les deux paires sont reliées par un ET : une vente n'est comptée que si elle est dans le Nord et porte sur le produit Premium en même temps. - 6Ferme la parenthèse et appuie sur Entrée.
=NB.SI.ENS(A2:A19; "Nord"; B2:B19; "Premium")
Comment compter le nombre de valeurs dans une fourchette avec la fonction NB.SI.ENS
L'audit interne te réclame le volume des transactions intermédiaires avant vendredi. Tes transactions sont listées avec leur montant, et tu veux le nombre de celles qui tombent entre 1 000 € et 5 000 € inclus, sans toucher au tri du tableau. La fonction NB.SI.ENS encadre la fourchette en testant deux fois la même colonne, une borne en bas et une borne en haut.
- 1Dans une cellule, écris
=NB.SI.ENS(. - 2En 1er argument : sélectionne la colonne des montants,
B2:B7. C'est sur elle que va porter la borne basse. - 3En 2ᵉ argument : écris le critère
">=1000", l'opérateur et la valeur seuil ensemble entre les mêmes guillemets. Seules les transactions à partir de 1 000 € restent retenues. - 4En 3ᵉ argument : sélectionne exactement la même plage,
B2:B7. Rien n'interdit de tester une colonne deux fois, et c'est même ce qui permet d'encadrer un intervalle. - 5En 4ᵉ argument : écris la borne haute,
"<=5000". Comme les deux paires sont reliées par un ET, seules les transactions comprises entre les deux seuils sont comptées. - 6Ferme la parenthèse et appuie sur Entrée.
=NB.SI.ENS(B2:B7; ">=1000"; B2:B7; "<=5000")>= et <=.
Comment utiliser des critères dynamiques avec la fonction NB.SI.ENS
Ton tableau de bord doit répondre à n'importe quelle question qu'on te posera en réunion, pas seulement à celle d'aujourd'hui. Tes commandes sont listées avec leur région et leur montant, et tu ranges la région à observer dans la cellule F1 (« Nord ») et le montant plancher dans F2 (10000). La fonction NB.SI.ENS lit ses critères dans ces deux cellules, si bien que le compte se recalcule dès que quelqu'un change un paramètre.
- 1Dans une cellule, écris
=NB.SI.ENS(. - 2En 1er argument : sélectionne la colonne des régions,
B2:B7. - 3En 2ᵉ argument : clique sur la cellule de paramètre
F1, au lieu d'écrire « Nord » en dur dans la formule. Un critère texte se passe très bien de guillemets quand il vient d'une cellule. - 4En 3ᵉ argument : sélectionne la colonne des montants,
C2:C7, qui couvre les mêmes lignes que la précédente. - 5En 4ᵉ argument : écris
">="&F2, l'opérateur entre guillemets et la cellule à l'extérieur, les deux collés par une esperluette. C'est la seule écriture qui fait comprendre « supérieur ou égal au contenu deF2». - 6Ferme la parenthèse et appuie sur Entrée.
=NB.SI.ENS(B2:B7; F1; C2:C7; ">="&F2)F1 et F2 (« Nord » et 10000). Deux commandes du Nord atteignent le plancher, d'où ce résultat de 2, et il suffit de taper « Sud » dans F1 pour que le compte se réajuste tout seul.
Comment compter le nombre de lignes entre deux dates avec les fonctions NB.SI.ENS et DATE
Le bilan du mois part demain matin et il te manque le nombre d'expéditions réellement sorties en janvier. Tes commandes sont listées avec leur date et leur statut, et tu veux ne compter que celles expédiées entre le 1er et le 31 janvier 2024, en laissant de côté celles de décembre, celles de février et celles encore en attente. La fonction NB.SI.ENS empile ici trois conditions, et la fonction DATE lui fournit des bornes qu'Excel reconnaît sans ambiguïté.
- 1Dans une cellule, écris
=NB.SI.ENS(. - 2En 1er argument : sélectionne la colonne des dates,
B2:B7. - 3En 2ᵉ argument : écris la borne de début,
">="&DATE(2024;1;1). La fonction DATE construit un vrai 1er janvier 2024, ce qui évite les mauvaises surprises d'un">=01/01/2024"qu'Excel peut prendre pour du texte. - 4En 3ᵉ argument : sélectionne à nouveau la colonne des dates,
B2:B7, pour refermer la fenêtre sur le mois. - 5En 4ᵉ argument : écris la borne de fin,
"<="&DATE(2024;1;31). Le dernier jour de janvier est construit lui aussi avec la fonction DATE. - 6En 5ᵉ argument : sélectionne la colonne des statuts,
C2:C7, toujours sur les mêmes lignes que les précédentes. - 7En 6ᵉ argument : écris le dernier critère,
"Expédiée". Les trois paires sont reliées par un ET : une commande n'est comptée que si elle tombe dans la fenêtre de janvier et qu'elle est bien partie. - 8Ferme la parenthèse et appuie sur Entrée.
=NB.SI.ENS(B2:B7; ">="&DATE(2024;1;1); B2:B7; "<="&DATE(2024;1;31); C2:C7; "Expédiée")
Les erreurs fréquentes avec la fonction NB.SI.ENS
Plages de tailles différentes : #VALEUR!
=NB.SI.ENS(A1:A10; "X"; B1:B15; "Y") provoque une erreur #VALEUR! car A1:A10 (10 lignes) et B1:B15 (15 lignes) n'ont pas la même dimension. NB.SI.ENS exige que toutes les plages de critères soient de même taille.
Solution : Vérifie que toutes tes plages commencent et finissent sur les mêmes numéros de ligne. Si ton tableau a 100 lignes de données, chaque plage doit couvrir exactement ces 100 lignes : A2:A101, B2:B101, C2:C101.
Ordre inversé plage/critère : résultat faux sans erreur
NB.SI.ENS attend toujours la plage EN PREMIER, puis le critère. Si tu inverses par habitude (critère puis plage), Excel peut interpréter une référence comme un critère et renvoyer un résultat incorrect sans message d'erreur.
Solution : Respecte toujours l'ordre plage; critère pour chaque paire. Rappel : c'est différent de SOMME.SI (qui accepte plage; critère; somme_plage), mais identique à SOMME.SI.ENS.
Concaténation opérateur/cellule mal formée : résultat 0
Écrire =NB.SI.ENS(A:A; >B1) sans guillemets autour de l'opérateur renvoie 0 ou une erreur, car Excel interprète >B1 comme une expression booléenne et non comme un critère texte.
Solution : Mets l'opérateur entre guillemets et concatène avec & : =NB.SI.ENS(A:A; ">"&B1). Vérifie aussi que la cellule de référence contient bien une valeur numérique et non du texte.
NB.SI.ENS vs NB.SI vs SOMME.SI.ENS vs SOMMEPROD
NB.SI.ENS est la fonction de comptage multi-critères standard. Voici quand utiliser chaque alternative.
| Critère | NB.SI.ENS | NB.SI | SOMME.SI.ENS | SOMMEPROD |
|---|---|---|---|---|
| Résultat | Comptage | Comptage | Somme | Produit ou comptage |
| Nombre de critères | Jusqu'à 127 | 1 seul | Jusqu'à 127 | Illimité |
| Logique OU | Non (ET uniquement) | Non | Non | Oui (avec +) |
| Caractères génériques | Oui | Oui | Oui | Non |
| Cas d'usage typique | Compter avec 2+ critères ET | Compter avec 1 critère | Additionner avec 2+ critères | Critères complexes, OU, calculs combinés |
Questions fréquentes
NB.SI.ENS applique un ET logique : une ligne n'est comptée que si toutes les conditions sont vraies simultanément.
Pour un OU, additionne plusieurs NB.SI ou NB.SI.ENS séparés : =NB.SI(A:A; "Paris") + NB.SI(A:A; "Lyon"). Si les deux conditions peuvent être vraies en même temps pour la même ligne, soustrais les doublons.
Oui, les jokers fonctionnent comme dans NB.SI. Le * remplace n'importe quel nombre de caractères : "*manager*" trouve tous les postes contenant le mot manager. Le ? remplace un seul caractère : "Davi?" trouve David mais pas Davidson.
Oui, c'est une règle stricte. Toutes les plages de critères doivent avoir exactement le même nombre de lignes et de colonnes. Si plage1 couvre A1:A100, alors plage2 doit aussi couvrir 100 lignes (par exemple B1:B100). Une seule plage de taille différente provoque une erreur #VALEUR!.
Utilise deux critères sur la même colonne de dates : =NB.SI.ENS(A:A; ">="&DATE(2024;1;1); A:A; "<="&DATE(2024;12;31)) compte toutes les dates de l'année 2024.
La fonction DATE garantit que les bornes sont de vraies dates reconnues par Excel, quelle que soit la façon dont les dates sont saisies dans ta feuille.
Pour aller plus loin
Continue sur ta lancée après la fonction NB.SI.ENS : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voirLes formules conditionnelles dans Excel
Le modèle Excel « Tableau de bord »
Le guide complet pour bien démarrer sur Excel
