«

»

Fonction NB.SI.ENS

Utilité de la fonction NB.SI.ENS

La fonction NB.SI.ENS vous permet de compter le nombre de lignes correspondant à des critères.

En fait, c'est un peu comme si vous effectuez un filtre sur vos données et que vous comptiez les lignes qui correspondent à vos critères de sélection. Sauf que là, la fonction fait le calcul pour vous 👍😍😎

NB.SI.ENS vous permet d'effectuer un dénombrement entre 1 et 255 critères. La fonction NB.SI ne peut le faire que sur un seul critère.

Pour reproduire les formules

Tout au long de cet article, vous pourrez essayer les différentes formules dans ce classeur (cellule orange 😉). Ce n'est pas une image mais bien un classeur intégré 👍🏆

Construction de la fonction NB.SI.ENS

Pour fonctionner, la fonction NB.SI.ENS a besoin

  • De la sélection d'une plage de données où se fera la recherche
  • Le critère de sélection pour cette plage de données
  • et ainsi de suite pour les 254 autres critères ...

Exemple sur 2 critères

Par exemple, nous allons partir de l'exemple suivant où vous souhaitez connaître le nombre d'hommes célibataires dans notre classeur.

  • Nous allons donc commencer par écrire le début de la formule
=NB.SI.ENS(
  • Ensuite, sélectionnez la plage de cellules pour y effectuer une sélection
=NB.SI.ENS(C2:C20;
  • Ensuite, vous allez saisir l'élément que vous voulez rechercher ; le terme "Homme".  Vous pouvez écrire directement ce terme en tant que critère ou bien sélectionner une cellule qui contient cette valeur.
=NB.SI.ENS(C2:C20;"Homme";

Le mot "Homme" s'écrit entre guillemets car il s'agit d'une chaîne de caractères et toutes les chaînes de caractères doivent être écrites entre guillemets dans Excel.

A la suite de ce premier critère, vous allez écrire votre deuxième critère

  • Sélectionner la colonne E comme deuxième plage de critère
  • Saisissez le mot "Célibataire" comme 2ème critère à rechercher

La fonction s'écrit au final de la façon suivante :

=NB.SI.ENS(C2:C20;"Homme";E2:E20;"Célibataire")

Remarque : Il est impératif que la taille de vos plages de cellules soit rigoureusement la même.

Par exemple =NB.SI.ENS(C2:C20;"Homme";E2:E21;"Célibataire") retournera l'erreur #/VALUE! car les 2 plages de cellules n'ont pas la même taille.

Pour aller plus loin

Il est possible de changer les paramètres du critère pour rendre votre recherche plus

Plus grand que / Plus petit que

Dans les critères de sélection, il est possible d'indiquer à Excel que nous voulons retourner le nombre de lignes qui ont un critère, plus grand ou plus petit d'une valeur.

Pour réaliser cela, il suffit d'écrire l'un de ces symboles logiques ">", "<", ">=", "<>". Mais attention, pour être interprétés, les symboles logiques doivent obligatoirement être écrits entre guillemets.

Par exemple, si nous souhaitons déterminer le nombre d'hommes qui ont un revenu supérieur ou égal à 50000, nous écrirons la formule suivante

=NB.SI.ENS(C2:C20;"Homme";D2:D20;">="&50000)

Sélection entre 2 valeurs

Pour effectuer un critère entre 2 valeurs, vous n'avez pas le choix. Vous devez écrire un premier critère plus grand que et un deuxième critère plus petit que.

Par exemple, si vous voulez connaître le nombre de personnes (hommes et femmes) qui ont un salaire compris entre 50000 et 70000, vous allez écrire :

=NB.SI.ENS(D2:D20;">=50000";D2:D20;"<=70000")

Créer un critère de type OU

Créer un test OU dans une fonction SI c'est facile. Mas avec la fonction NB.SI.ENS c'est plus compliqué.

Tous les paramètres utilisés dans une fonction NB.SI.ENS sont des opérateurs logiques ET (tous les critères sont évalués). Pour pouvoir retourner le résultat de l'équivalent d'un OU, il faut en fait écrire deux fois la formule NB.SI.ENS pour chaque cas à traiter.

Si nous voulons connaître le nombre d'hommes célibataires ou divorcés, nous allons d'abord faire une fonction pour dénombrer le nombre d'hommes célibataires puis une seconde fonction pour dénombrer le nombre d'hommes divorcés.

L'addition des 2 fonctions va nous donner le nombre d'hommes célibataires ou divorcés.

=NB.SI.ENS(C2:C20;"Homme";E2:E20;"Célibataire")+NB.SI.ENS(C2:C20;"Homme";E2:E20;"Divorcé")


Critère sur une partie d'une donnée

En informatique, pour effectuer une recherche partielle sur un terme, on utilise des métacaractères. Il y en a 2 :

  • * pour remplacer tout ou partie d'une chaîne de caractère
  • ? pour remplacer un seul caractère

Le symbole ? est très peu utilisé mais le symbole * est vraiment très utile.

Le symbole * permet de remplacer tout ou partie d'une chaîne de caractères. C'est-à-dire que si vous voulez compter le nombre de personnes qui ont un prénom qui commence par la lettre S, vous allez écrire la formule

=NB.SI.ENS(B2:B20;"S*") => Résultat 2

Pour connaître le nombre de personnes qui ont un nom qui se termine par Y, vous allez écrire la formule (le symbole * est utilisé une seule fois)

=NB.SI.ENS(A2:A20;"*Y") => Résultat 3

Pour connaitre le nombre de personnes dont le nom contient la lettre H (au début, au milieu ou à la fin du nom), la formule s'écrit de la façon suivante en utilisant 2 fois le symbole *

=NB.SI.ENS(A2:A20;"*H*")

Dans notre exemple, il y en a 3 : JANJUAH (H à la fin), CHAMPNEY (H au milieu), CHAP (H au milieu).

Essayez ces 3 formules dans le classeur de test en haut de cet article ; elles marchent 🏆😉

Articles complémentaires


Voici une liste d'articles qui pourrait également vous intéresser sur le même thème.

Lien Permanent pour cet article : https://www.excel-exercice.com/nb-si-ens/


(48 commentaires)

Passer au formulaire de commentaire

  1. hakem

    =nb.si.ens(d8:d1000;"3m";e8:fp8;"fr") le résultats message valeur et ceci pour calculer la somme des absences dans le mois de chaque classe

  2. Mickaël

    Bonjour,

    Je vous écris car j'aimerais plus de précision sur l'utilisation de nb.si.ens avec des dates.
    En effet, j'aimerais compter le nombre de valeurs d'une colonne, si les valeurs sont comprises entre dateA et dateB.

    J'imaginais donc écrire
    NB.SI.ENS(A3:A500;">= " & dateA ; A3:A500;"= " & dateA) fonctionne chez moi.
    Mais si j'écris
    NB.SI.ENS(A3:A500;">= 01/01/2017") ça ne fonctionne pas. Sauriez-vous me dire pourquoi?

    J'ai l'impression qu'il y a une syntaxe très précise avec les dates et les espaces à respecter, mais je ne trouve pas.

    Merci d'avance pour votre aide

    1. Frédéric LE GUEN

      Ca devrait. Il ne faut pas mettre d'espaces entre la date et les signes logiques.

  3. Fabrice

    Bonjour ,

    j'ai un tableau excel avec des noms en colonne de gauche , des horaires de travail pour le mois pour chaque personne et un compteur en bas de chaque jours qui me donne le nombre de personne effectuant le mème horaire de travail.
    Je souhaite avoir un tableau qui me donne le nom des personne travaillant dans un horaire de travail donné.

    Merci pour votre aide

  4. Debard

    Bonjour,

    Je n'arrive pas à faire une formule. Je suis prof d'EPS et je voudrais créer une formule qui me permet de connaître sur la 2eme mi temps d'un match combien il y a eu de nouveaux tireurs dans une équipe... mais je bloque !

    Je pensais que la formule suivante pouvais fonctionner mais non :

    =SI(plage=0;NB.SI(autre plage;">0")) mais j'ai un message d'erreur ....

    Est ce que quelqu'un peut m'aider !?

    D'avance merci

  5. ACHACHI

    Bonjour,

    Pourriez-vous m'aider svp?

    dans mon tableau j'ai une colonne intitulée C6/NF et une autre avec le montant
    soit on renseigne le code C6 soit le Code NF
    sur chaque ligne on renseigne donc soit C6 soit NF et donc le montant attribué sera pour l'un ou pour l'autre
    jusque là tout va bien 😉

    mais je dois renseigner dans un autre tableau une autre réf à savoir j'ai le montant global C6 / et le montant global NF alors
    dans la cellule objectif C6 = si la ligne 3 = C6 alors on prend on compte le montant si non (donc NF) on ne prend rien donc 0
    à l'inverse dans la cellule objectif NF si la ligne 4 = NF elle sera comptabilisée sinon 0
    Je ne sais pas si vous pouvez m'aider (si je suis claire ) ??
    Merci d'avance

  6. p.ducommun

    Monsieur,
    j'ai 8 cellules de F17 à M17
    Dans ces colonnes, j'ai des prix différents.
    Dans la cellule N17, je souhaite afficher le prix le plus bas.
    Jusque là, pas de problèmes, la formule =MIN(F17;M17) me convient.
    Cela se gâte au moment ou une colonne, qui n'a pas de prix ou = à CHF 0.-, car la cellule N17 affiche 0.- plutôt que, le prix le plus bas
    des 7 autres cellules comportant un prix supérieur à 0.-

    Une solution me comblerait.

    Patrick

  7. Anonyme

    Bonjour,
    J'ai un problème pour lequel je demande si quelqu'un a une solution qu'il m'aide.

    J'ai un tableau contenant :
    -une liste de travailleurs journaliers
    -Les dates de recrutement de chaque personnes. Chaque personne peut être recrutée à plusieurs reprises.

    J'aimerais avec une formule pouvoir compter le nombre de personnes (sans doublons) qui sont recrutées pour chaque mois (Janvier, Février, Mars...)

    Merci de votre aide.

  8. TRAORE

    inpecable de toutes les informations sur ces deux fonctions en Excel. courage

  9. MFrance

    Bonjour Frédéric, j'ai un tableau avec une colonne contenant beaucoup de dates (date de production) - dont des doublons; une autre colonne avec le nom du mois (texte) (les mois ne correspondent pas exactement aux dates, certaines exceptions s'appliquent à l'aide d'une fonction RECHERCHEV). Dans une troisième colonne je dois connaître le nombre de jours de production du mois corrigé en question (ne doit pas inclure les doublons). Ce chiffre me servira à calculer plusieurs autres données. Mais là là je suis bloquée! Est-ce possible de mettre vos lumière sur ce problème? Je vous remercie,

  10. HAFID AIT TALB

    bsr stp une solution pour cette formule
    soit une nombre des unites M
    si M est entre 1 et 5 alors resultat est M *3
    si M est entre 6 et 17 alors resultat sera 5*3 plus le reste (M-5) multiplier 4
    si M egale 18 ou plus alors resultat sera 5*3 plus 17*4 et le reste multiplier sur 5

  11. creveladalle

    Bonjour
    merci beaucoup pour la clareté des explications. Juste une petite coquille dans la formule dénombrer sur une période de temps je crois qu'il faut manque quelques points virgules (;) pour séparer les critères et plages car on voit des virgules.

  12. krimopoulos

    Bonjour j'ai un fichier avec la date et l'heure d'entrée et la date et l'heure de sortie de patients aux urgences sur 1 mois.
    je souhaiterais savoir combien de patient sont présents en moyenne par tranche horaire sur 24h.
    est ce que la fonction NB.SI.ENS est la bonne formule ?

  13. Nico

    Bonjour,

    je voudrai integrer la possibilité de multiplier ma matrice résultante de la fonction NB.SI.ENS par une autre matrice.
    Je m'explique; j'ai une colonne assez simple, dont je peux vérifier facilement le critere, j'ai une autre colonne, sur laquelle je dois rechercher la presence de caractere pour chaque cellule. Jusqu'ici, la fonction NB.SI.ENS le gère bien. Mais je veux, avant d'additionner ce croisement, multiplier le résultat de chaque ligne par une 3eme matrice. Je veux en fait additionner les valeurs de la 3eme colonne si je respecte les conditions 1 et 2...
    J'ai essayer de passer par la fonction sommeprod, mais la, c'est la condition sur les caractere que je n'ai pas reussi à faire resortir sous format matriciel...

    Voici ma formule sous format NB.SI.ENS mais qui ne prend pas en compte ma 3eme colonne (AI)
    =NB.SI.ENS($AL$10:$AL$209;(C14&D14);$AD$10:$AD$209;"*19" & E14 & "*")

    Voici la formule sous format sommeprod, mais ma condition sur ma seconde colonne (AD) représentée en NB.SI me renvoie une valeur et pas une matrice
    =SOMMEPROD(($AL$10:$AL$209=(C15&D15))*NB.SI($AD$10:$AD$209;"*19" & E15 & "*");($AI$10:$AI$209))

    Si quelqu'un a une idée...

  14. Julien

    Bonjour,

    J'ai créer une matrice de contrôle pour mon travail, pour détecter le nombre d'erreur.

    La matrice fonctionne correctement, toutefois dans le but d'optimiser celle-ci, je souhaite savoir si il existe une formule ou une macro permettant de retrouver les références comptabilisées par la formule NB.SI.ENS.

    Je pourrais passer par un tableau croisé dynamique, mais cette matrice est utilisée par plusieurs utilisateurs et je souhaite l'automatisé le plus possible.

    Pouvez-vous m'aider sur le sujet ?

    Julien

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *