Fonction NB.SI.ENS en EXCEL pour les nuls (et les autres)

La fonction Excel NB.SI.ENS permet de compter les cellules correspondant à plusieurs critères. Il s'agit d'un dérivé de la fonction Excel plus connue: NB.SI…

NB.SI.ENS peut être utilisée pour compter des valeurs lorsque les cellules correspondantes répondent en même temps à des conditions (jusqu'à 127) basés sur des dates, des nombres, du texte ou même des formules. Cette fonction prend en charge les opérateurs logiques (>,<,<> et =) et les caractères génériques (* et ?) pour une correspondance partielle.

Syntaxe de la fonction NB.SI.ENS

Et voici la syntaxe de la fonction à utiliser dans Excel:
=NB.SI.ENS(Plage_critères1;Critères1;Plage_critères2;Critères2; … ;Plage_critères127;Critères127)
Pour information, l'équivalent en anglais de NB.SI.ENS est COUNTIFS

Les arguments de la fonction NB.SI.ENS en détail

La fonction NB.SI.ENS contient 2 arguments obligatoires et jusqu'à 126 paires d'arguments optionnels. Les voici donc en détail…

• Plage_critères1 : ceci est la plage avec les données qui doivent remplir le première critère. Il peut s'agir des données de texte, des dates, des nombres. À nouveau, il est possible d'indiquer cette plage comme une adresse ou comme le nom d'une plage nommée. Cet argument est obligatoire.
• Critères1 : Il s'agit de la condition que les valeurs de Plage_critères1 doivent remplir pour être reprises dans le résultat final. Ce critère peut contenir des dates, des nombres, des textes, des valeurs booléennes ( VRAI / FAUX ), une formule ou une référence à une cellule. Les textes et les dates doivent être entre parenthèses, contrairement aux nombres et les booléens. Si vous omettez l'opérateur, c'est l'opérateur = qui sera utilisé. Si vous souhaitez utiliser d'autres opérateurs, l'argument DOIT se trouver entre parenthèses. Voir exemples d'utilisation plus loin. Pour les textes, vous pouvez utiliser les caractères de remplacement ? et * (voir également dans les exemples).
• Plage_critères2 : ceci est la plage avec les données qui doivent remplir le deuxième critère. Cet argument se comporte de la même manière que Plage_critères1 avec la différence que celui-ci est optionnel.
• Critères2 : la deuxième condition que les données doivent remplir. Cet argument se comporte de la même manière que Critères1 sans être obligatoire.
• ...
• Plage_critères127 : La dernière plage de données à pouvoir être utilisée. Se comporte exactement comme Plage_critères2.
• Critères127 : La dernière condition à pouvoir être utilisée. Se comporte exactement comme Critères2

Le résultat renvoyé par la fonction

Le résultat de la fonction NB.SI.ENS est représenté par le nombre d'occurrences qui remplissent TOUTES les conditions listées dans les arguments de la fonction. Si une des conditions n'est pas remplie, l'occurrence ne sera pas incluse dans le résultat.

Informations pratiques sur l'utilisation de NB.SI.ENS

Comme vous pouvez le constater, vous pouvez utiliser au maximum 127 conditions (concernant 127 plages de données – le plus souvent des colonnes – différentes) mais vous devez en utiliser au moins une. Si vous voulez utiliser uniquement une seule condition, il vous suffira d'utiliser la fonction NB.SI. La fonction NB.SI.ENS n'a de sens qu'à partir de deux conditions à remplir.

Faites particulièrement attention aux arguments contenants les conditions (Critères1, Critères2,…) car dans la majorité des cas d'erreurs, le problème vient justement de là.

Voici des points d'attention pour éviter les soucis et les résultats incorrects:

• Si plusieurs conditions sont appliquées, c'est la logique ET qui est appliquée, c'est-à-dire la condition 1 ET la condition 2, etc.
• Chaque plage supplémentaire doit avoir le même nombre de lignes et de colonnes que Plage_critères1, mais les plages n'ont pas besoin d'être adjacentes. Si vous indiquez des plages dont la taille ne correspond pas, la fonction se soldera par l'erreur #VALEUR!.
• Les chaînes de texte dans les critères doivent être placées entre guillemets (""). En utilisant un opérateur, la condition (même numérique) se comporte comme un texte. Exemples: "rouge", ">500", "*AB*"
• Les références de cellules dans les critères ne sont pas entre guillemets! Seul l'opérateur est alors entre guillemets. Exemple ">"&C5
• Les caractères génériques ? et * peuvent être utilisés dans les critères. Un point d'interrogation (?) correspond à n'importe quel caractère et un astérisque (*) correspond à n'importe quelle séquence de caractères (zéro ou plus). Attention, ces deux caractères ne peuvent être utilisés qu'avec des textes. Vous ne pouvez pas les utiliser ni pour trouver des nombres (ex. "*8" pour trouver 28 ou 178 ne va pas fonctionner) ni pour trouver des dates (ex. "*-11-2021" pour trouver toutes les occurrences ayant lieu en novembre 2021 ne marchera pas non plus)
• Pour trouver un point d'interrogation ou un astérisque dans un texte, utilisez un tilde (~) devant le point d'interrogation ou l'astérisque (c'est-à-dire ~?, ~*) pour éviter qu'il soit considéré comme un caractère générique…

Exemples d'utilisation pratique de NB.SI.ENS

Penchons-nous maintenant sur sur les différents cas pratiques de l'utilisation de cette fonction. Pour faciliter cela, j'ai préparé un classeur Excel avec des données avec lequel nous allons pouvoir tester les différentes possibilités.
Vous pouvez    télécharger le fichier DÉMO ici .

Il s'agit d'un tableau reprenant des ventes de 3 produits différents (cahier, crayon et stylo), vendus de deux manières différentes (an magasin et en ligne) pendant une période de temps.

Si vous utilisez les données de ce Classeur, vous pourrez utiliser les formules qui suivent telles quelles…

Cas le plus simple – deux conditions basiques

Commençons par un cas simple: Calculer le nombre des ventes des cahiers faites en ligne. (Attention, il ne s'agit pas de trouver le nombre de cahiers vendus mais bien du nombre des ventes!)

Pour cela, nous aurons alors deux conditions: article = "cahier" et type de vente = "En ligne".

La formule sera ensuite la suivante:

=NB.SI.ENS(B2:B27;"cahier";C2:C27;"En ligne") et le résultat sera 4

Si vous souhaitez placer "cahier" et "En ligne" dans des cellules (disons par exemple H3 et H6) plutôt que directement dans la formule pour pouvoir modifier votre recherche plus facilement, la formule sera la suivante:

=NB.SI.ENS(B2:B27;H3;C2:C27;H6)

Et maintenant un exemple avec un argument numérique…

Si vous souhaitez compter les ventes en ligne dont le montant total a été de 9,00 €, voici la formule:

=NB.SI.ENS(C2:C27;"En ligne";F2:F27;9) qui donnera comme résultat 2

Comme vous pouvez le remarquer, le 4^ème argument (c'est-à-dire le "critère" de la 2^ème condition) a la valeur 9

Utiliser NB.SI.ENS avec des opérateurs

Maintenant quelques exemples d'utilisation avec les différents opérateurs:

1. Nombre des ventes en ligne avec un total supérieur à 15 €
   • La formule: =NB.SI.ENS(C2:C27;"En ligne";F2:F27;">15″)
   • L'argument avec opérateur: ">15" (entouré de guillemets!)
   • Résultat: 12
2. Nombre des ventes en ligne où on a vendu autre chose que des stylos
   • La formule: =NB.SI.ENS(C2:C27;"En ligne";B2:B27;"<>stylo")
   • L'argument avec opérateur: "<>stylo"
   • Résultat: 8
3. Le nombre des ventes de crayons où on a vendu au moins 5 pièces d'un coup
   • La formule: =NB.SI.ENS(B2:B27;"crayon";D2:D27;">=5″)
   • L'argument avec opérateur: ">=5"
   • Résultat: 5

NB.SI.ENS avec des comparaisons avec des valeurs dans une autre cellule

Un cas très pratique et qui prête souvent à confusion est quand la valeur de comparaison n'est  pas une valeur fixe mais le contenu d'une cellule. Par exemple "si la valeur est plus petite que la valeur dans la cellule B1".

La règle est facile: Il faut mettre l'opérateur entre guillemets, suivi de & et du nom de la cellule. Donc par exemple "<" & B1

Et maintenant quelques exemples:

1. Nombre des ventes en ligne avec un total supérieur à la valeur de la cellule "K1"
   • La formule: =NB.SI.ENS(C2:C27;"En ligne";F2:F27;">" & K1)
   • L'argument avec opérateur: ">" & K1 (Attention! Uniquement l'argument est entre guillemets!)
2. Nombre des ventes en ligne où on a vendu autre chose que l'article dont le nom se trouve dans la cellule "J1"
   • La formule: =NB.SI.ENS(C2:C27;"En ligne";B2:B27;"<>" & J1)
   • L'argument avec opérateur: "<>" & J1

Conditions avec des caractères génériques * et ?

Maintenant, quelques exemples des conditions textuelles avec des caractères génériques qui permettent de faire la somme des cellules basée sur des textes similaires:

1. Le nombre des ventes en magasin des produits commençant par la lettre c
   • La formule: =NB.SI.ENS(C2:C27;"Magasin";B2:27;"c*")
   • L'argument avec opérateur: "c*" (entouré de guillemets)
   • Résultat: 6
   • Remarque: l'astérisque remplace un nombre non-spécifié de caractères
2. Nombre des ventes d'articles dont le nom est composé de 6 caractères vendus en ligne
   • La formule: =NB.SI.ENS(C2:C27;"En ligne";B2:B27;"??????")
   • L'argument avec opérateur: "??????"
   • Résultat: 8
   • Remarque: l' ? remplace exactement 1 caractère. C'est pour cela qu'ici, on va utiliser la condition avec 6 points d'interrogation
3. Les ventes inférieures à 20,00 € des produits dont le nom contient la lettre "y"
   • La formule: =NB.SI.ENS(F2:F14;"<20″;B2:B14;"*y*")
   • L'argument avec opérateur: "*y*"
   • Résultat: 20

Conditions avec des DATES dans la fonction NB.SI.ENS

Pour pouvoir utiliser les dates dans vos conditions, vous avez deux possibilités:

• pour une condition "avant une date" ou "après une date", il vous suffit d'utiliser simplement les opérateurs numériques < ou > (éventuellement <= ou => )
• pour une durée de temps délimitée (comme par exemple: "tout le mois de décembre 2021"), vous devrez utiliser deux conditions différentes avec les deux dates qui délimite la période souhaitée. Attention donc, les caractères génériques * et ? ne fonctionnent pas avec des dates. Oubliez donc des conditions comme "*-12-2021"…

Voici donc quelques exemples:

1. Nombre des ventes supérieures à 35,00 € après le 20/11/2021
   • La formule: =NB.SI.ENS(F2:F27;">35″;A2:A27;">20/11/2021″)
   • L'arguments avec opérateur: ">35" et ">20/11/2021" (entourés de guillemets car contiennent un opérateur)
   • Résultat: 3
   • Remarque: Excel reconnaît les dates d'après leur format. Comme Excel utilise plusieurs formats de dates, il en reconnaît plusieurs également. L'utilisation de ">20-11-2021" à la place de ">20/11/2021" aurait conduit exactement au même résultat
2. Nombre des ventes en ligne faites en octobre 2021
   • La formule: =NB.SI.ENS(C2:C27;"En ligne";A2:A27;">=1/10/2021″;A2:A27;"<=31/10/2021″)
   • Les arguments avec opérateur: ">=1/10/2021" et "<=31/10/2021" (entourés de guillemets car contiennent un opérateur)
   • Résultat: 8
   • Remarque 1: vous remarquez que nous avons utilisé 3 conditions différentes
   • Remarque 2: les deux conditions de dates – "après 1/10/2021 inclus" et "avant 31/10/2021 inclus" sont utilisées avec la logique ET ce qui permet de délimiter la période souhaité
3. Le nombre des ventes des crayons en dehors de la période 15/10/2021 à 15/11/2021
   • La formule: =NB.SI.ENS(B2:B27;"crayon";A2:A27;"<15-10-2021″)+NB.SI.ENS(B2:B27;"crayon";A2:A27;">15-11-2021″)
   • Les arguments avec opérateur: "<15-10-2021" et ">15-11-2021"
   • Résultat: 4
   • Remarque: comme les conditions sont évalués selon la logique condition 1 ET condition 2 ET ..., le résultat des conditions combinées de notre exemple ne sera jamais positif dans une seule fonction NB.SI.ENS. Il est donc nécessaire d'utiliser deux décomptes différents (un avant la période à exclure et un après cette période) et on additionne les deux décomptes. En fait, il serait possible de n'utiliser qu'une seule fonction NB.SI.ENS pour obtenir le bon résultat mais il faudrait passer par une "formule matricielle" mais on abordera ce sujet assez complexe dans un autre tutoriel…

Utilisation de NB.SI.ENS dans un Tableau Excel

Jusqu'ici, nous avons utilisé dans les formules les plages désignées par leur adresse (par exemple A2:A50). Mais il est également possible d'utiliser les plages nommées.
Et, comme 3^ème possibilité, nous pouvons utiliser la fonction NB.SI.ENS (comme les autres fonctions Excel) dans un "Tableau".
Le comportement des fonctions est le même, il est juste nécessaire de faire attention à la syntaxe des formules. Chaque colonne d'un Tableau Excel est mentionné comme: NomDeTableau[NomDeColonne]
Dans notre exemple, le tableau s'appelle "Exemples". La fonction NB.SI.ENS aura alors la forme suivante:

=NB.SI.ENS(Exemples[Article];"stylo";Exemples[Type de vente];"En ligne")

et son résultat sera, le même que quand on utilise les adresses…

L'avantage de l'utilisation d'un Tableau est que les formules sont facilement lisibles et il est facile de se faire l'idée des conditions utilisées, surtout si on ne connaît pas par cœur l'ordre de colonnes des données utilisées

Conclusion de ce tutoriel

On arrive à la fin de ce tutoriel. J'espère que la fonction NB.SI.ENS n'a plus de secret pour vous maintenant. Il s'agit d'une fonction moins connue d'Excel mais est tout de même assez pratique et qui peut vous permettre de travailler facilement avec vos données.

Pour aller plus loin dans Excel…

Si vous avez apprécié ce tutoriel, je vous suggère de lire également les articles suivants car ils pourraient aussi vous intéresser et vous être utiles…

• Liste de toutes les fonctions Excel (les informations sur les 481 fonctions disponibles dans Excel)
• Le plus complet des tutoriels sur les Tableaux Croisés Dynamiques Excel
• Tutoriels sur les principales fonctions: SI, NB.SI, SOMME.SI, SOMME.SI.ENS, RECHERCHEV, RECHERCHEX, TROUVE,…
• EXCEL en ligne GRATUIT – comment l'utiliser
• Calcul de la Moyenne pondérée en Excel
• Quelques outils Excel gratuits et pratiques à télécharger

Et pour information: la page sur le site de Microsoft dédiée à NB.SI.ENS…