NB.SI plusieurs critères : NB.SI.ENS et autres solutions

Il est possible de créer une formule Excel permettant de générer une foncton NB.SI avec plusieurs critères.

Fonctions utilisées :
Télécharger le fichier
NB.SI plusieurs critères - NB.SI.ENS et autres solutions

La fonction NB.SI permet de compter le nombre de valeurs dans une plage de données en fonction d’un critère sur cette plage. Elle est très pratique dès lors que vous voulez dénombrer rapidement des catégories dans vos colonnes.

Cependant, cette fonction ne permet de saisir qu’un seul critère. Heureusement, dès que l’on veut faire un NB.SI plusieurs critères, il existe une solution très facile : la fonction NB.SI.ENS. Elle vous permettra de répondre à votre besoin dans 90% des cas. Elle permet de compter le nombre de lignes répondant à un ensemble de critères que vous aurez définis.

La fonction NB.SI.ENS ne suffira malheureusement pas dès que vous aurez des critères qui s’excluent mutuellement. En effet, si vous voulez la liste des salariés faisant partie du service Production OU du service Logistique, il vous faudra trouver d’autres solutions.

Dans ce tutoriel, je vous explique comment répondre à la majorité des cas. Si vous vous retrouvez à devoir mettre des conditions OU (critère 1 ou critère 2…) je vous indique des solutions viables.

La fonction NB.SI

Syntaxe

La fonction NB.SI permet de compter le nombre d’enregistrements dans un tableau correspondant à un critère que vous aurez préalablement défini. Ce critère peut être appliqué sur des dates, du texte ou des nombres.

La syntaxe de la formule est : 

= NB.SI ( plage ; critère )

Les arguments de la fonction NB.SI sont : 

  • plage : il s’agit des coordonnées des cellules sur lesquels nous souhaitons appliquer le critère ;
  • critère : condition que les cellules doivent remplir pour être comptées

Exemple

Pour illustrer notre tutoriel, nous allons nous servir d’un tableau contenant des salariés, leurs rémunérations, leurs dates d’entrée et leurs services. Pour ce 1er exemple, nous allons compter le nombre de salariés faisant partie du service Production.

La syntaxe à saisir de votre barre de formule est la suivante : 

=NB.SI(E3:E27;"Production")

Les arguments saisis ont été : 

  • plage : E3:E27 – il s’agit des coordonnées des cellules sur lesquelles nous voulons vérifier que la valeur saisie est “Production”
  • critère : “Production” – il s’agit d’un texte et doit donc être mis entre guillemets
Utilisation de la fonction NB.SI avec un seul critère

La fonction NB.SI.ENS

Syntaxe

La fonction NB.SI.ENS permet de compter le nombre de lignes répondant à un ensemble de critères que nous allons définir. Sa syntaxe est un peu différente de celle de la fonction NB.SI mais reste très facile d’utilisation.

= NB.SI.ENS ( plage_critère1 ; critère1 ; plage_critère2 ; critère2 ; … )

Seuls les deux premiers arguments sont obligatoires : 

  • plage : il s’agit des coordonnées des cellules sur lesquels nous souhaitons appliquer le critère ;
  • critère : condition que les cellules doivent remplir pour être comptées

Vous l’aurez compris, même si l’essence de cette fonction est d’appliquer plusieurs critères, elle n’en exige qu’un pour fonctionner correctement. Pratique !

Pour ajouter d’autres critères, il suffira de saisir à chaque fois la plage de données puis le critère. Attention, toutes les plages de données doivent avoir les mêmes dimensions (mêmes lignes de départ et d’arrivée). Cependant, rien ne vous empêche de calculer de manière horizontale (en ligne) et auquel cas, vous devrez avec les mêmes dimensions horizontalement (même colonne de départ et d’arrivée).

La fonction NB.SI.ENS considère que tous les critères sont cumulatifs. Il n’est pas possible que vous lui demandiez de compter le nombre de salariés faisant partie du service Production ainsi que du service Logistique. Pour cela, il faudra vous référer aux autres solutions expliquées plus loin dans ce tutoriel.

Exemple

Reprenons notre tableau avec les salariés. Nous voulons obtenir le nombre de salariés faisant partie du service production et arrivés après le 1er Janvier 2022. La syntaxe de la formule sera la suivante :

=NB.SI.ENS(E3:E27;"Production";D3:D27;">="&DATE(2022;1;1))
Utilisation de la fonction NB.SI.ENS avec 2 critères

Les arguments saisis ont été : 

  • plage_critère1 : E3:E27
  • critère1 : « Production »
  • plage_critère2 : D3:D27
  • critère2 : « >= »&DATE(2022;1;1)

La saisie des conditions

Si vous n’êtes pas à l’aise avec la syntaxe des critères, voici un tableau vous résumant comment saisir vos conditions. Il s’agit de test logiques.

TypeSyntaxeSignification
Texte“Production”la valeur est égale à Production
“<>Production”la valeur est différente de Production
“*Production*”la valeur contient Production
“Production*”la valeur commence par Production
“*Production”la valeur se termine par Production
Date“>”&DATE(2022;1;1)Supérieur au 01/01/2022 
“>=”&DATE(2022;1;1)Supérieur ou égal au 01/01/2022 
“<”&DATE(2022;1;1)Inférieur au 01/01/2022 
“<=”&DATE(2022;1;1)Inférieur ou égal au 01/01/2022 
DATE(2022;1;1)Date exacte
Nombres“>”&2000Supérieur à 2000
“>=”&2000Supérieur ou égal à 2000
“<”&2000Inférieur à 2000
“<=”&2000Inférieur ou égal à 2000
2000Montant exact
Autres conditions“=”Cellule vide (pas de formule ni de valeur nulle)
“”Valeur nulle (peut être le résultat d’une formule)
“<>”Cellule non vide (inclus zéro)

Faire une fonction NB.SI.ENS pour obtenir une NB.SI avec plusieurs critères

Je vais à présent vous fournir plusieurs exemples de l’utilisation de la fonction NB.SI.ENS pour obtenir des NB.SI plusieurs critères. Vous verrez comment appliquer des ccritères sur du texte, des dates et des nombres.

Exemple 1 – Critères sur des nombres

Dans un premier temps, nous voulons calculer le nombre d’enregistrements correspondant aux critères suivants : 

  • Salaire supérieur ou égal à 1800€
  • Service Production

La syntaxe de la formule sera la suivante : 

=NB.SI.ENS(C3:C27;">="&1800;E3:E27;"Production")
Utilisation de la fonction NB.SI.ENS avec un critère sur un nombre et un critère sur du texte

Notez que la condition appliquée au montant a été écrite de la façon suivante “>=”&1800. J’aurais pu l’écrire “>=1800”.

Exemple 2 – Critères sur des dates

Je vais complexifier les critères appliqués sur les dates puisque je vais vouloir les salariés entrés dans les effectifs en 2022. C’est-à-dire que je veux appliquer les 2 conditions suivantes : 

  • date d’entrée supérieure ou égale au 01/01/2022
  • date d’entrée inférieure ou égale au 31/12/2022

Voici la syntaxe de la formule : 

=NB.SI.ENS(D3:D27;">="&DATE(2022;1;1);D3:D27;"<="&DATE(2022;12;31))
Utilisation de la fonction NB.SI.ENS avec des critères sur des dates

Si vous souhaitez indiquer des conditions en comparant les valeurs par rapport à la date du jour, il faut utiliser la fonction AUJOURDHUI.

Exemple 3 – Critères sur des cellules vides

Dans certains cas, vous devez travailler avec des plages de données contenant des cellules vides. Dans le prochain exemple, je vais compter le nombre de salariés entrés en 2022 et n’ayant pas de service affecté.

Voici la formule : 

=NB.SI.ENS(E3:E27;"";D3:D27;">="&DATE(2022;1;1);D3:D27;"<="&DATE(2022;12;31))
Utilisation de la fonction NB.SI.ENS avec des cellules vides

Faire une fonction NB.SI avec plusieurs critères et des conditions OU (critère 1 ou critère 2…)

Depuis le début de ce tutoriel Excel, nous avons étudié comment ajouter des critères à la fonction NB.SI et NB.SI.ENS. Ces critères s’aditionnent entre eux. Cependant, que faire lorsque nous voulons le nombre de salariés appartenant au service Production ou au service Logistique? Nous allons voir comment gérer ces cas.

Solution 1 – Cumuler les NB.SI

La première solution est assurément la plus facile et elle sera amplement suffisante dans la majorité des cas. Il s’agit simpliement de cumuler les fonctions NB.SI et d’additionner leurs résultats. En voici un exemple : 

=NB.SI(E3:E27;"Production")+NB.SI(E3:E27;"Logistique")
Mettre plusieurs NB.SI dans la formule pour mettre plusieurs critères

Solution 2 – Utiliser un argument matriciel

Bien que l’exemple précédent soit suffisant dans la majorité des cas, il peut arriver que le nombre de possibilités (de critères) soit trop important et rende la formule illisible.

Heureusement, vous avez la possibilité de saisir un argument matriciel. Pour cela, vous devez indiquer entre crochers {} vos conditions en les séparant par des points-virgules comme dans l’exemple suivant : 

{“Production”;”Logistique”}

Ensuite, il va falloir mettre le tout dans une formule combinant la fonction SOMME et NB.SI de la façon suivante : 

=SOMME(NB.SI(E3:E27; {"Production";"Logistique"};C3:C27))
Utilisation de la fonction NB.SI avec un argument matriciel pour mettre plusieurs critères

Cette solution est vraiment sympathique à utiliser et très élégante. Par contre, retenir sa syntaxe n’est pas évident. Heureusement, il nous reste une dernière solution.

Solution 3 – Utiliser SOMMEPROD

Cette dernière solution utilise la fonction SOMMEPROD. Son grand intérêt réside dans le fait de pouvoir mettre les critères dans des cellules dédiées et de les appeler dans la formule. C’est bien pratique lors des évolutions successives de vos tableaux. La syntaxe de la formule sera : 

=SOMMEPROD(NB.SI(H10:H11;E3:E27))

Dans cette formule, les coordonnées H10:H11 sont les cellules dans lesquelles j’ai indiqué les différents services (“production” et “logistique”).

Voici le résultat : 

Utilisation des fonctions SOMMEPROD et NB.SI pour mettre plusieurs critères

La fonction NB.SI.ENS avec plusieurs critères avec conditions OU (critère 1 ou critère 2…)

Il n’y a pas qu’avec la fonction NB.SI que nous pouvons appliquer des conditions de type OU. Nous pouvons le faire avec la fonction NB.SI.ENS. Rassurez-vous, les solutions sont identiques.

Solution 1 – Cumuler les NB.SI.ENS

Tout comme avec la fonction NB.SI, ne résistez pas à la facilité et cumulez les fonctions NB.SI.ENS. Par exemple, pour avoir le nombre de salariés arrivés en 2022 et faisant partie du service Logistique ou Production, il me suffit de mettre dans la même formule deux fonctions NB.SI.ENS.

= NB.SI.ENS(D3:D27;">="&DATE(2022;1;1);D3:D27;"<="&DATE(2022;12;31);E3:E27;"Logistique")

+NB.SI.ENS(D3:D27;">="&DATE(2022;1;1);D3:D27;"<="&DATE(2022;12;31);E3:E27;"Production")

Utilisation de plusieurs NB.SI.ENS pour mettre plusieurs critères

Cette solution reste la plus pratique lorsque notre liste est restreinte et peu évolutive. Dans tous les autres cas, il faudra se diriger vers les prochaines solutions.

Solution 2 – Utiliser un argument matriciel

Ici on garde les mêmes principes que lors de l’utilisation de l’argument matriciel avec la fonction NB.SI. Voici la syntaxe :

=SOMME(NB.SI.ENS(D3:D27;">="&DATE(2022;1;1);D3:D27;"<="&DATE(2022;12;31);E3:E27;{"Production";"Logistique"}))
Utilisation des fonctions SOMME et NB.SI.ENS avec un argument matriciel

Solution 3 – Utiliser SOMMEPROD

Tout comme avec la fonction NB.SI, il est possible d’utiliser la fonction SOMMEPROD avec la fonction NB.SI.ENS. De cette façon vous pourrez appeler une liste de conditions directement détenues dans des cellules. Voici la syntaxe : 

=SOMMEPROD(NB.SI.ENS(D3:D27;">="&DATE(2022;1;1);D3:D27;"<="&DATE(2022;12;31);E3:E27;I7:I8))
Utilisation des fonctions SOMMEPROD et NB.SI.ENS pour mettre plusieurs critères dans des cellules

Conclusion

Nous voila à la fin de ce tutoriel. Vous savez tout à présent des différentes solutions pour effectuer des NB.SI plusieurs critères que ces critères soient cumulables (ET) ou non (OU). Ne résistez pas à la facilité lors de la mise en œuvre de la solution. Les solutions les plus simples sont souvent les meilleures… ou du moins les plus simples à maintenir.

4.8/5 - (24 votes)

Vous avez apprécié cet article ?

Pour me soutenir, cliquez sur cette publicité. Pas besoin d’acheter, un clic suffit.

Merci. 🤩

Annonces

Modèles Excel

+

Livret 

7 méthodes pour gagner 2H par semaine sur Excel

C’est gratuit 🤩

Les formations

Formations Excel

Parce que chacun devrait avoir les moyens de faire son travail sur Excel

Découvrez des formations accessibles, rapides et directement applicables.