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
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))
Les arguments saisis ont été :
plage_critère1: E3:E27critère1: « Production »plage_critère2: D3:D27critè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.
| Type | Syntaxe | Signification |
| 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 | “>”&2000 | Supérieur à 2000 |
| “>=”&2000 | Supérieur ou égal à 2000 | |
| “<”&2000 | Inférieur à 2000 | |
| “<=”&2000 | Inférieur ou égal à 2000 | |
| 2000 | Montant 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")
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))
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))
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’additionnent 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")
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))
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 :
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")
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"}))
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))
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.
Formules en lien
- Compter le nombre de cellules égales à un texte
- Compter le nombre de cellules contenant du texte
- Compter le nombre de cellules contenant une partie du texte (mot, caractère)
- Compter nombre de valeurs texte différentes
- Compter le nombre de cellules non vides
- Compter le nombre de cellules contenant une formule
- Somme si non vide
- SOMME.SI.ENS avec plusieurs critères sur la même colonne
- SOMMEPROD avec SI
- Trouver des doublons avec NB.SI
- MAX sensible à la casse
- MAX.SI.ENS sensible à la casse
- Compter le nombre de cellules contenant un texte différent de
Retrouvez d’autres astuces dans l’article sur la fonction NB.SI.
