«

»

Fonction NB.SI.ENS

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

NB.SI.ENS vous permet d’effectuer un dénombrement entre 1 et 255 critères. La fonction NB.SI (fonction obsolète ⛔) ne pouvait faire un dénombrement que sur un seul critère.

Support de cours

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 …

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.

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)

Créer un critère 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 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

Autant créer un test OU dans une fonction SI est facile, autant avec la fonction NB.SI.ENS cela 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 🏆😉




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

(42 commentaires)

1 ping

Passer au formulaire de commentaire

  1. 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

  2. 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.

  3. TRAORE

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

  4. 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,

  5. 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

  6. 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.

  7. 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 ?

  8. 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…

  9. 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

  10. Arno

    Bonjour, merci pour toutes ces indications sur la fonction NB.SI.ENS
    De mon côté je me heurte au problème suivant :
    Si on part du premier tableau d’exercice de la page, comment compter par exemple : le nombre d’hommes célibataires OU mariés ?
    La difficulté vient de ce que j’ai trois critères : “homme”, “mariés” et célibataire” et deux plages. Ainsi “célibataires” et “mariés” font référence à une seule et même plage. Comment faire ? Merci pour votre aide. A

  11. Ray_78

    Bonjour, est il possible de combiner cette fonction avec une critère de recherche de type “cherche” ??

  12. Pat54

    Bonjour,
    Je cherche une formule qui me permettrait de calculer le nombre de personnes présentes durant une plage horaire (par exemple entre 17h et 22h).
    Merci par avance.
    Pat

    1. Frédéric LE GUEN

      Bonjour,
      Oui, c’est bien la fonction NB.SI.ENS qu’il faut utiliser mais après il faut faire attention à ce que vos données des heures soient correctes. Si vous mettez 17, Excel comprendra qu’il s’agit de 17 jours. Pour indiquer que ce sont des heures il faut convertir vos données en divisant par 24

    2. krimopoulos

      avez vous reussi votre formule ? ça m’interesse ?
      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 ?

  13. marie-josee

    bonjour j’ai fait un tableau afin de calculer les heures pour des maladies des congé ou du temps repris… cela fonctionne par couleur je voudrais faire un calcule de toute les cases rouges dans lequel j’aurais inscrit le nombre d’heures que la personne a pris…
    rouge= maladie
    jaune= vacances
    vert= temps repris

    je ne trouve pas de formule… Merci de bien vouloir m’aider

    1. Frédéric LE GUEN

      Il n’y a pas de fonction pour dénombrer le nombre de couleur dans une colonne.

  14. bea

    Bonjour,
    J’ai un tableau avec une plage de cellule correspondante à des trevenus moyens par pays.
    Dans certains cellules j’ai pas des valeur mais du text (n.d). Dans un autre tableau il faut que je trouve le taux de répartition (le revenu d’un pays sur la totalité de tous les pays)
    =SOMME(C58;1/(C58:C85) mais par tout je recois la même valeur de 1 ou ca donne une valeur. Comment faire obtenir une valeur correcte en comptant les cellules où il n’y a pas de valeur numérique?
    Merci d’avance pour votre réponse!

  15. melleprincesse

    Bonjour,

    Je souhaite faire une formule NB.SI pour des ensembles de nombre :
    Par exemple, je souhaite avoir le nombre de cellules comportant des chiffres compris entre 10 et 15, j’ai essayé d’insérer ceci :
    NB.SI.ENS(E33:E51); 10<E:33:E51<15)
    Ca n'a bien sur pas marché, pouvez-vous m'aider ?
    Merci à vous

    1. Frédéric LE GUEN

      Erreur dans les parenthèses et dans l’écriture
      =NB.SI.ENS(E33:E51;”>=10″;E33:E51;”<15")

  16. Anonyme

    Dans la fonction NB.SI.ENS, est-il possible de remplacer un symbole (> = etc par la référence de la cellule correspondante Par exemple <30 s'écrirait &B28&C28 sachant que B28 contient < et C28 contient 30

    1. Frédéric LE GUEN

      Oui c’est tout à fait possible 🙂

      1. Bernard

        MERCI la formule fonctionne et va bien me faciliter le travail

  17. zramos

    Bonjour,

    Je souhaite utiliser cette fonction sauf que j’arrive pas.

    J’ai les numéros de commandes depuis le mois de janvier a Décembre.

    j’ai des commandes qui commencent de janvier et finissent en Mars ou Avril.

    Ce que je souhaite faire est de prendre en considération que le 1er mois de la signature de la commande et de ne pas prendre en considération les autres mois.

    Merci de m’aider ou plus d’info si je ne suis pas claire

  18. Christelle Paniagua

    Bonsoir,

    Je souhaite utiliser la fonction =NB.SI.ENS dans une “Validation de Données”, afin d’obtenir le message (Doublon)
    Mon problème :
    Colonne A = 10032002 (je l’appelle n° de section)
    Colonne B = (valeur à ne pas chercher)
    Colonne C = (valeur à ne pas chercher)
    Colonne D = (valeur à ne pas chercher)
    Colonne E = 57850 (je l’appelle n° de facture)

    Dans la colonne A on retrouve toujours les même nombre (10032002 ; 10032003 ; 10032004)
    Sauf que dans la colonne E, j’impute les numéro de facture sur chaque section.
    En règle générale, il y a 1 n° de facture pour 1 n° de section, dans ce cas je peux utiliser la formule : =NB.SI($E$2:$E$99999;E2)=1
    pour que Excel m’empeche de saisir 2 fois la même facture dans la colonne E.

    Mais sachant que je suis amener à saisir 2 fois la meme facture en colonne E sur 2 section différente en colonne A, comment puis-je demander à Excel de vérifier les doublons sur 2 colonnes différentes ?

    Je m’explique, je souhaite que Excel me dise :

    Colonne A Colonne B Colonne C Colonne D Colonne E

    10032002 57801
    10032002 57802
    10032003 57803
    10032002 57804 DOUBLON VALIDE
    10032003 57804 DOUBLON VALIDE
    10032004 57805
    10032002 57806
    10032002 57801 ERREUR DOUBLON

    C’est pourquoi j’ai pensé à la fonction =NB.SI.ENS puisqu’il y a plus d’un critère à rechercher.

    Pouvez-vous m’aider ?

  19. Estelle

    Bonjour,

    Voici mon problème, je cherche à calculer ma “date prévisionnelle”, elle correspond à:
    date demandé date confirmé date révisé date prévisionnel
    23/01/2015 23/01/2015
    18/02/2015 09/03/2015 09/03/2015
    15/02/2015 17/02/2015 18/02/2015 18/02/2015

    Je m’explique si il y a une date confirmé, alors on prendra la date confirmé au lieu de la date demandé.
    Mais s’il y a la date révisé on prendra la date révisé au lieu de la date demandé et de la date confirmé.

    J’espère être assez claire.
    J’arrive avec deux colonnes (=SI(ESTTEXTE(B2)=FAUX;A2;B2)), mais pas les trois.

    Pouvez-vous m’aider?

    1. Frédéric LE GUEN

      Bonjour,
      Il faut faire un SI imbriqué. Vous en avez un exemple ici http://www.excel-exercice.com/fonction/condition/fonction-si/#Cas_pratique_Match_de_football pour gérer 3 événements. C’est exactement le même fonctionnement dans votre cas

  20. M.F

    Bonjour,

    Je n’arrive pas à faire la formule d’après vos exemples pourtant clair ….
    En colonne C j’ai l’âge de personnes, et en colonne G un quartier, j’aimerai compter le nombre de personnes par tranches d’âge et par quartier. Avec comme tranche d’âge 12-16 ans, 17-25 ans, 26-60 ans et + de 60 ans.

    Par exemple avoir le total de personnes dans la tranche 17-25 ans dans le quartier “Toukin”

    Merci d’avance pour votre aide.

  21. NB

    Bonjour,
    Je cherche désespérément le moyen de réaliser cette opération pour une même case (par exemple B2) mais pour plusieurs pages?
    J’ai donc une série d’onglets de même structure où dons chaque onglet, on a répondu à une question par OUI ou NON. Je voudrais pouvoir compter facilement le nombre de OUI…
    La formule “basique” =NB.SI(“nom du premier onglet”:”nom du dernier onglet”!B2;”oui”) ne fonctionne pas 🙁
    Des idées??
    Merci beaucoup.
    NB

    1. Frédéric LE GUEN

      Bonjour,
      Il est tout à fait possible de faire cela mais il faut passer par une formule un peu plus complexe qui utilise les fonctions SOMMEPROD, NB.SI et INDIRECT
      En reprenant votre exemple de la cellule B2, il faut au préalable copier les noms de feuille dans des cellules (ici A12 jusqu’à A14) et appliquer la formule suivante
      =SOMMEPROD(NB.SI(INDIRECT(“‘”&A12:A14&”‘!B2″);”OUI”))
      La solution provient du site de mrexcel.com http://www.mrexcel.com/forum/excel-questions/806004-count-x-survey-1-across-mulitple-sheets.html

  22. yaro

    je voudrais ajouter un total de lettres a un total en chiffre.. nombre de P pour présent et 4.5 pour les temps partiel, je pensais utiliser NB SI est ce possible ou bien quelle est la solution

  23. Alexandra

    Bonjour,
    Comment faire pour utiliser NB.SI.ENS si on a largement plus de 255 valeurs ? J’en ai plus de 600 000…
    Penser-vous que l’on doive passer par un code type MatLab et faire un batch ?
    Merci beaucoup

    1. Frédéric LE GUEN

      En lisant votre question, je comprends que vous confondez les critères de sélection avec le nombre de lignes á traiter. Vous pouvez tout á fait faire la fonction pour 600 000 lignes mais c’est certains que vous allez avoir des lenteurs pour traiter autant de lignes.

  24. Michel

    Bonjour,
    Je cherche la fonction ou la formule qui synthétise dans un tableau à part le résultat d’un NB.SI.ENS. Par exemple je veux savoir dans un autre tableau les noms des hommes mariés, ou en concubinage ou autre sans faire de filtre car le résultat, je l’exploite dans un autre onglet.
    Merci de m’aider.
    Cordialement

    1. Frédéric LE GUEN

      Bonjour,
      L’exemple que je donne est exactement le problème que vous évoquer. Il suffit juste de construire votre tableau dans un onglet séparer et ça fonction. Mais attention, si vous faîtes le tableau de synthèse dans un autre classeur, cela ne marchera pas. NB.SI.ENS ne fonctionne pas avec un classeur fermé (la fonction a été conçu comme cela, volontairement)

  25. Gildas

    Bonjour Frederic,
    Jai en H5 une cellure contenant un mois de l’annee, disons Mars
    En I5 une cellure contenant un montant, disons $20
    De J4 a J16 les douzes mois de l’annee
    Ma question ; comment remplir les cases J5 a J16 , sachant que chaque case doit tenir compte du mois en H5, que les cases avant Mars doivent etre egales a 0 mais que les cases de Mars a Decembre doivent etre egale a $20??

    Merci d avance

    1. Frédéric LE GUEN

      Seule la fonction SI est à utiliser dans ce cas de figure avec les références des cellules bloquées http://www.excel-exercice.com/fonction/recherche-reference/reference-absolue-relative/
      La formule peut s’écrire = SI($H$5>=$J5,20$,0)

      1. Gildas

        Merci Frederic,
        Finalement j’avais trouve tout seul en lisant ton site!
        Tu me sauves ! 😉

  26. Rachid

    Je vous adore, Merci beaucoup pour ces informations ils m’ont étaient très utiles.

  27. KOUEGO

    bonjour

    j’ai ce fichier dans lequel j’aimerai utiliser la fonction somme.si.ens dans la formule: =SOMME.SI.ENS($G$2:$G$59;$B$2:$B$59;”Communicación”;$F$2:$F$59;”28-09-12″)
    Elle est partiellement correcte pour ce que je veut faire. Mon problème se situe au niveau de la date. Je voudrai remplacer le “jour 28” par “quelque soit” de telle sorte que la dans la plage de cellule F2 à F59 que tous les élements qui ont trait à septembre 2012 soient sélectionnés.
    Parce que dans l’état où est la formule cela ne sélectionne que les éléments du 28 septembre or je voudrai tous les éléments “Communicación” de septembre dans le cas où il y en aurait plusieurs.

    1. Frédéric LE GUEN

      Il faut mettre 2 fois une condition sur la date. J’ai présenté la solution dans l’explication de la fonction NB.SI.ENS
      http://www.excel-exercice.com/fonction/condition/nb-si-ens/#toc-3

  28. Anonyme

    =NB.SI.ENS(E33:E51;OU(“Célibataire”;”Divorcé”))
    Est ce normal que le ou de cette formule ne marche pas

  29. menardeau

    Bonjour,

    j’ai un tableau avec une plage de donnée correspondant aux mois de prélevement d’un produit

    j’ai fais un autre tableau plus synthétique où j’aimerai faire apparaître le mois que le service prélève.

    Merci de m’aider ou plus d’info si je ne suis pas clair

  1. Excel sur votre portable gratuitement - Excel Exercice

    […] Fonction NB.SI.ENS […]

Laisser un commentaire

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

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Advertisment ad adsense adlogger