9 cas qui expliquent comment utiliser la fonction NB.SI.ENS

L’article présent de la formation Excel vous fait découvrir comment utiliser la fonction NB.SI.ENS pour appliquer plusieurs critères aux plages de cellules. Les critères qui seront traités dans les 9 exemples d’utilisation de NB.SI.ENS qui suivent, sont de types texte, numérique, date … Cet article va vous montrer aussi comment compter le nombre de cellules non vides correctement, et comment utiliser NB.SI.ENS avec OU logique.

La fonction NB.SI.ENS vous permet de compter le nombre de cellules répondant à plusieurs critères de types différents : Nombre, Texte, Date, valeur logique….

La syntaxe de la fonction NB.SI.ENS est :

NB.SI.ENS(plage_critères1; critères1; [plage_critères2; critères2]…)

Les deux arguments plage_critères1 et critères1 sont obligatoires au fonctionnement de la fonction NB.SI.ENS, quant aux autres paires plages_critères/critères, elles sont facultatives.

Combien de paires plages-critères/critères pouvez-vous introduire dans la fonction NB.SI.ENS ?

• La fonction NB.SI.ENS peut contenir jusqu’à 127 paires plages-critères/critères.

Note : la plage critères doit être insérée toujours avant le critère associé.

Pour découvrir rapidement comment utiliser la fonction NB.SI.ENS, regardez cette vidéo :

Pour bien comprendre comment utiliser la fonction NB.SI.ENS, veuillez suivre ces 9 exemples :

• Partons de ce petit tableau et entamons le premier exemple :

Exemple 1 : Combien de vendeurs ont réalisé un montant de vente plus que 3000 Euros à Paris ?

• Sélectionnez une cellule pour y insérer la formule suivante :

=NB.SI.ENS(C2:C14;"Paris";D2:D14;">3000")

Cette formule permet de sélectionner en premier les cellules contenant « Paris » dans la plage de cellules C2:C14, puis de trouver les montants supérieurs à 3000 Euros dans la plage de cellules D2:D14 et qui correspondent à ces cellules.

Note : pour le premier critère "Paris", il est écrit entre guillemets puisque c’est un texte. Et pour le deuxième critère ">3000", il est écrit aussi entre guillemets parce que le nombre 3000 est précédé d’un opérateur de comparaison ">".

Normalement lorsqu’un critère est de type numérique, on l’écrit sans guillemets, mais si vous lui faites accompagner <,>, = ou <> vous devez le mettre entre guillemets.

Exemple 2 : Utilisation des références de cellules dans les arguments de la fonction NB.SI.ENS

Dans cet exemple, nous avons écrit les critères Nom de ville dans la cellule F4, et le montant de vente dans F6.

Au lieu de modifier à chaque fois notre formule exemple de NB.SI.ENS, nous allons tout simplement changer les données dans ces deux cellules F4 et F6 :

• Voici donc notre formule dynamique que nous allons utiliser :

=NB.SI.ENS(C2:C14;F4;D2:D14;">"&F6)

• Remarque n°1 : le critère nom de la ville est remplacé par la référence de cellule F4 sans guillemets.
• Remarque n°2 : pour le critère >3000, nous avons laissé l’opérateur > entre guillemets et avons ajouté une esperluette suivie de la référence F6 sans guillemets.

Essayons maintenant de remplacer Paris par Lisbonne et remarquez que le résultat est mis à jour automatiquement :

Note : ne mettez pas une référence de cellules entre guillemets lorsque vous l’utilisez comme critère de la fonction NB.SI.ENS.

Exemple 3 : Combiner des astérisques avec des références de cellules

Par exemple, si vous voulez compter le nombre de cellules contenant le nom Antoine qui travaille à Lisbonne, tapez la formule suivante :

=NB.SI.ENS(B2:B14;"*"&F6&"*";C2:C14;F4)

Vous voyez les deux esperluettes qui se sont placées avant et après la référence de cellule F6.

Exemple 4 : Compter le nombre de cellules contenant des montants entre 2000 et 4000 Euros

Dans cet exemple, vous allez traiter deux critères de type numériques et qui se trouvent dans la même colonne ou plage de cellules.

• Commencez d’abord par donner à chaque colonne de votre tableau un nom significatif que vous allez utiliser comme référence dans les arguments de votre fonction NB.SI.ENS. ça va vous faciliter le travail !

Par exemple renommer la plage :

• A2:A14 par Date_Vente
• B2:B14 par Vendeurs
• C2:C14 par Villes
• D2:D14 par Montants.

• Sélectionnez une cellule vide et tapez =NB.SI.ENS( puis tapez  mon  pour faire apparaître le nom de la colonne Montants.
• Sélectionnez-le donc et continuez la saisie de votre formule pour obtenir la formule suivante :

=NB.SI.ENS(Montants;">2000";Montants;"<4000")

Attention !

Il faut que les plages de critères utilisées dans la fonction NB.SI.ENS aient le même nombre de lignes, si non Excel retournera une erreur de type #VALEUR !.

Utilisation des critères de type date dans la fonction NB.SI.ENS

Exemple 5 : Compter le nombre de ventes réalisées entre le 05/04/2017 et le 08/04/2017

• Sélectionnez une cellule vide et entrez la formule suivante :

=NB.SI.ENS(A2:A14;">=05/04/2017";A2:A14;"<=08/04/2017")

• Faites attention aux guillemets !

Exemple 6: Compter le nombre de ventes réalisées à Londres depuis le 05/04/2017 jusqu’aujourd’hui et ayant un montant supérieur à 2000 Euros

Notre formule va contenir donc trois plages de critères : Date de vente, Ville et Montant de vente et la fonction AUJOURDHUI() :

• Sélectionnez une cellule vide et entrez la formule suivante :

=NB.SI.ENS(Date_Vente;">=05/04/2017";Date_Vente;"<="&AUJOURDHUI();Ville;C14;Montants;">2000")

• L’objectif de cet exemple est de vous présenter une fonction NB.SI.ENS contenant quatre critères de 3 types de données différents.

NB.SI.ENS différent de

Exemple 7 : Compter le nombre de cellules contenant toutes les noms de villes sauf  "Paris" et dont la date de vente est postérieure ou égale à 05/04/2017

• Entrez la formule suivante en introduisant l’opérateur <> :

=NB.SI.ENS(Ville;"<>paris";Date_Vente;">=05/04/2017")

Exemple 8 : Compter le nombre des cellules non vides

Vous avez vu dans l’article: "NB, NBVAL et NBVIDE comptent le nombre de cellules différemment" comment compter le nombre de cellules non vides en utilisant la fonction NBVAL. Et pour le faire avec la fonction NB.SI.ENS, tapez la formule suivante qui va vous permettre de compter le nombre de montants de ventes réalisées à Londres en ignorant les montants non encore enregistrés :

• Par exemple la cellule D14 est vide.

=NB.SI.ENS(Ville;"Londres";Montants;"<>"&"")

• Remarquez donc que le critère vide est exprimé par les deux guillemets ""
• Remarquez aussi que nous avons mis une esperluette & entre l’opérateur <> et le critère vide pour que la fonction renvoie le bon résultat.

NB.SI.ENS avec OU

Vous avez sans doute constaté que tous les critères utilisés dans la fonction NB.SI.ENS sont évalués dans les exemples précédents, alors que parfois vous souhaitez que NB.SI.ENS soit effectuée en répondant à un seul caractère au moins,  tout comme l’utilisation d’un OU logique.

Pour utiliser donc la fonction NB.SI.ENS avec OU, la solution consiste à faire l’addition des fonctions NB.SI.ENS associée chacune à son critère.

Exemple 9 : Compter le nombre de cellules contenant Paris et Londres dont les montants réalisés sont supérieurs à 2000 Euros.

• Sélectionnez une cellule vide et entrez la formule suivante :

=NB.SI.ENS(Ville;"Paris";Montants;">2000")+NB.SI.ENS(Ville;"Londres";Montants;">2000")

Dans cet exemple nous avons utilisé une fonction NB.SI.ENS avec le critère Paris et une autre avec le critère Londres, et les deux contiennent aussi le même critère >2000

Pour vous assurer bien de la fonctionnalité de cette formule, faites un filtre des données comme s’est illustré dans l’image animée suivante :

La fonction NB.SI.ENS et le VBA

Terminons cet article par une solution à un petit problème qui gêne beaucoup les utilisateurs de VBA Excel (surtout les novices !) lorsqu’ils veulent utiliser la fonction NB.SI.ENS dans leur code VBA.

L’erreur qu’ils commettent c’est qu’ils utilisent la nomination en français de la fonction NB.SI.ENS dans leurs codes et bien sûr ces codes ne fonctionneront pas bien.

C’est tout à fait normal car dans VBA, ils doivent taper la fonction NB.SI.ENS en anglais comme ça : COUNTIFS.

PARTAGEZ PARTAGEZ PARTAGEZ