Comment utiliser plusieurs critères dans Excel COUNTIF et COUNTIFS

Excel a de nombreuses fonctions où un utilisateur doit spécifier un ou plusieurs critères pour obtenir le résultat. Par exemple, si vous souhaitez compter les cellules en fonction de plusieurs critères, vous pouvez utiliser les fonctions COUNTIF ou COUNTIFS dans Excel.

Ce tutoriel couvre différentes façons d’utiliser un ou plusieurs critères dans COUNTIF et COUNTIFS fonction dans Excel.,

bien que je me concentre principalement sur les fonctions COUNTIF et COUNTIFS dans ce tutoriel, tous ces exemples peuvent également être utilisés dans d’autres fonctions Excel qui prennent plusieurs critères comme entrées (telles que SUMIF, SUMIFS, AVERAGEIF et AVERAGEIFS).

Ce tutoriel couvre:

une Introduction aux fonctions Excel COUNTIF et COUNTIFS

commençons par comprendre l’utilisation des fonctions COUNTIF et COUNTIFS dans Excel.,

fonction Excel COUNTIF (prend des critères uniques)

la fonction Excel COUNTIF est la mieux adaptée aux situations où vous souhaitez compter les cellules en fonction d’un seul critère. Si vous souhaitez compter en fonction de plusieurs critères, utilisez la fonction COUNTIFS.

Syntaxe

=NB.SI(gamme, critères)

les Arguments d’Entrée

plage – la plage de cellules dont vous voulez compter.
critères – les critères qui doivent être évalués par rapport à la plage de cellules pour qu’une cellule soit comptée.,

fonction Excel COUNTIFS (prend plusieurs critères)

la fonction Excel COUNTIFS est la mieux adaptée aux situations où vous souhaitez compter les cellules en fonction de plusieurs critères.

syntaxe

=COUNTIFS(criteria_range1, criteria1,…)

Arguments D’entrée

criteria_range1 – la plage de cellules pour lesquelles vous souhaitez évaluer par rapport à criteria1.
criteria1-les critères que vous souhaitez évaluer pour criteria_range1 pour déterminer les cellules à compter.
– la plage de cellules pour lesquelles vous souhaitez évaluer par rapport aux critères2.,
– Les critères que vous souhaitez évaluer pour criteria_range2 pour déterminer les cellules à compter.

examinons maintenant quelques exemples d’utilisation de plusieurs critères dans les fonctions COUNTIF dans Excel.

utilisation des critères de nombre dans les fonctions COUNTIF Excel

#1 compter les cellules lorsque Criteria est égal à une valeur

pour obtenir le nombre de cellules où l’argument criteria est égal à une valeur spécifiée, vous pouvez saisir directement les critères ou utiliser la référence de cellule qui contient les critères.,

Voici un exemple où nous comptons les cellules qui contiennent le nombre 9 (ce qui signifie que l’argument criteria est égal à 9). Voici la formule:

=NB.SI($B$2:$B$11,D3)

Dans l’exemple ci-dessus (dans la photo), les critères dans la cellule D3. Vous pouvez également entrer les critères directement dans la formule., Par exemple, vous pouvez également utiliser:

=NB.SI($B$2:$B$11,9)

#2 Compter les Cellules lorsque les Critères sont supérieurs à une Valeur

Pour obtenir le nombre de cellules avec une valeur supérieure à une valeur spécifiée, nous utilisons l’opérateur supérieur à (« >”). Nous pourrions l’utiliser directement dans la formule ou utiliser une référence de cellule qui a les critères.

chaque fois que nous utilisons un opérateur dans des critères dans Excel, nous devons le mettre entre guillemets doubles., Par exemple, si le critère est plus grand que 10, donc nous avons besoin d’entrer « >10 » comme critère (voir photo ci-dessous):

Voici la formule:

=NB.SI($B$2:$B$11, »>10″)

Vous pouvez aussi avoir des critères dans une cellule et l’utilisation de la cellule de référence comme critère. Dans ce cas,vous n’avez pas besoin de mettre les critères entre guillemets doubles:

=COUNTIF (B B 2 2: B B$11, D3)

Il pourrait également y avoir un cas où vous voulez que les critères soient dans une cellule, mais ne le voulez pas avec l’opérateur., Par exemple, vous pouvez vouloir que la cellule D3 porte le numéro 10 et non >10.

Dans ce cas, vous devez créer un argument critères qui est une combinaison de l’opérateur et de la cellule de référence (voir photo ci-dessous):

=NB.SI($B$2:$B$11,”>”&D3)

REMARQUE: Lorsque vous combinez un opérateur et d’une cellule de référence, l’opérateur est toujours entre guillemets doubles. L’opérateur et la référence de cellule sont reliés par une esperluette (&).,

#3 Compter les Cellules lorsque les Critères est inférieure à une Valeur

Pour obtenir le nombre de cellules avec une valeur inférieure à une valeur spécifiée, nous utilisons le moins que l’opérateur (« <« ). Nous pourrions l’utiliser directement dans la formule ou utiliser une référence de cellule qui a les critères.

chaque fois que nous utilisons un opérateur dans des critères dans Excel, nous devons le mettre entre guillemets doubles., Par exemple, si le critère est que le nombre doit être inférieur à 5, alors nous avons besoin d’entrer « <5 » que les critères (voir photo ci-dessous):

=NB.SI($B$2:$B$11, »<5″)

Vous pouvez aussi avoir des critères dans une cellule et l’utilisation de la cellule de référence comme critère., Dans ce cas,vous n’avez pas besoin de mettre les critères entre guillemets (voir photo ci-dessous):

=COUNTIF (B B 2 2: B B$11, D3)

de plus, il peut y avoir un cas où vous voulez que les critères soient dans une cellule, mais ne le voulez pas avec l’opérateur. Par exemple, vous pouvez vouloir que la cellule D3 porte le numéro 5 et non <5.,

Dans ce cas, vous devez créer un argument critères qui est une combinaison de l’opérateur et de la cellule de référence:

=NB.SI($B$2:$B$11,”<« &D3)

#4 compter les cellules avec plusieurs critères – entre deux valeurs

pour obtenir un nombre de valeurs entre deux valeurs, nous devons utiliser plusieurs critères dans la fonction COUNTIF.

Voici deux méthodes pour le faire:

méthode 1: Utilisation de la fonction COUNTIFS

la fonction COUNTIFS peut gérer plusieurs critères en tant qu’arguments et ne compte les cellules que lorsque tous les critères sont vrais., Pour compter les cellules avec des valeurs comprises entre deux valeurs spécifiées (par exemple, 5 et 10), on peut utiliser les éléments suivants COUNTIFS fonction:

=COUNTIFS($B$2:$B$11, »>5″,$B$2:$B$11, »<10″)

NOTE: La formule ci-dessus n’est pas de compter les cellules qui contiennent 5 ou 10. Si vous souhaitez inclure ces cellules, utilisez des opérateurs supérieurs à (>=) et inférieurs à (<=)., Voici la formule:

=COUNTIFS($B$2:$B$11, »>=5″,$B$2:$B$11, »<=10″)

Vous pouvez également disposer de ces critères dans les cellules et l’utilisation de la cellule de référence comme critère. Dans ce cas, vous n’avez pas besoin de mettre les critères entre guillemets (voir photo ci-dessous):

Vous pouvez également utiliser une combinaison de références de cellules et d’opérateurs (où l’opérateur est entré directement dans la formule). Lorsque vous combinez un opérateur et une référence de cellule, l’opérateur est toujours entre guillemets., L’opérateur et la référence de cellule sont reliés par une esperluette (&).

méthode 2: Utilisation de deux fonctions COUNTIF

Si vous avez plusieurs critères, vous pouvez utiliser COUNTIFS ou créer une combinaison de fonctions COUNTIF., La formule ci-dessous devraient aussi faire la même chose:

=NB.SI($B$2:$B$11, »>5″)-NB.SI($B$2:$B$11, »>10″)

Dans la formule ci-dessus, nous trouvons d’abord le nombre de cellules qui ont une valeur supérieure à 5 et on soustrait le nombre de cellules avec une valeur supérieure à 10. Cela nous donnerait le résultat 5 (qui est le nombre de cellules qui ont des valeurs supérieures à 5 et inférieures à 10).,3c219a21b4″>

Si vous voulez la formule pour inclure à la fois le 5 et le 10, utilisez la formule suivante à la place:

=NB.SI($B$2:$B$11, »>=5″)-NB.SI($B$2:$B$11, »>10″)

Si vous voulez la formule pour exclure les deux ‘5’ et ’10’ à partir du comptage, utilisez la formule suivante:

=NB.SI($B$2:$B$11, »>=5″)-NB.SI($B$2:$B$11, »>10″)-NB.SI($B$2:$B$11,10)

Vous pouvez avoir ces critères dans les cellules et d’utiliser les cellules de références, ou vous pouvez utiliser une combinaison des opérateurs et des cellules de références.,

utilisation des critères de texte dans les fonctions Excel

#1 compter les cellules lorsque Criteria est égal à un texte spécifié

pour compter les cellules qui contiennent une correspondance exacte du texte spécifié, nous pouvons simplement utiliser ce texte comme critère. Par exemple, dans l’ensemble de données (illustré ci-dessous dans l’image), si je veux compter toutes les cellules avec le nom Joe, je peux utiliser la formule ci-dessous:

=COUNTIF (B B 2 2: B B B 11,”Joe”)

comme il s’agit d’une chaîne de texte, je dois mettre les critères de texte entre guillemets.,

Vous pouvez également avoir les critères dans une cellule,puis utiliser cette référence de cellule (comme indiqué ci-dessous):

=COUNTIF (B B 2 2: B B B 11, E3)

REMARQUE: Vous pouvez vous tromper résultats s’il y a des espaces de début/fin dans les critères ou la plage de critères. Assurez-vous de nettoyer les données avant d’utiliser ces formules.

#2 compter les cellules lorsque Criteria n’est pas égal à un texte spécifié

semblable à ce que nous avons vu dans l’exemple ci-dessus, vous pouvez également compter les cellules qui ne contiennent pas de texte spécifié., Pour ce faire, nous avons besoin d’utiliser l’opérateur différent de (<>).

Supposons que vous souhaitez compter toutes les cellules qui ne contiennent pas le nom de JOE, voici la formule qui va le faire:

=NB.SI($B$2:$B$11,”<>Joe”)

=COUNTIF (B B 2 2: B B$11, E3)

Il pourrait également y avoir un cas où vous voulez que les critères soient dans une cellule mais ne le veulent pas avec l’opérateur. Par exemple, vous pouvez vouloir que la cellule D3 porte le nom Joe et non <>Joe.,

Dans ce cas, vous devez créer un argument critères qui est une combinaison de l’opérateur et de la cellule de référence (voir photo ci-dessous):

=NB.SI($B$2:$B$11,”<>”&E3)

Lorsque vous combinez un opérateur et d’une cellule de référence, l’opérateur est toujours entre guillemets doubles. L’opérateur et la référence de cellule sont reliés par une esperluette (&).

utilisation des critères de DATE dans les fonctions Excel COUNTIF et COUNTIFS

Excel stocke la date et l’heure sous forme de nombres., Nous pouvons donc l’utiliser de la même manière que nous utilisons les nombres.

#1 compter les cellules lorsque Criteria est égal à une Date spécifiée

pour obtenir le nombre de cellules contenant la date spécifiée, nous utiliserions l’opérateur égal à (=) avec la date.

pour utiliser la date, je recommande d’utiliser la fonction DATE, car elle élimine toute possibilité d’erreur dans la valeur de date. Ainsi, par exemple, si je veux utiliser la date du 1er septembre 2015, je peux utiliser la fonction DATE comme indiqué ci-dessous:

=DATE(2015,9,1)

Cette formule renverrait la même date malgré les différences régionales., Par exemple, le 01-09-2015 serait le 1er septembre 2015 selon la syntaxe de la date américaine et le 09 janvier 2015 selon la syntaxe de la date britannique. Cependant, cette formule reviendrait toujours le 1er septembre 2105.

Voici la formule pour compter le nombre de cellules qui contiennent la date 02-09-2015:

=NB.SI($A$2:$A$11,DATE(2015,9,2))

#2 Compter les Cellules lorsque les Critères sont AVANT ou APRÈS une Date Spécifiée

Pour compter les cellules qui contiennent la date avant ou après une date spécifiée, on peut utiliser le supérieur/inférieur opérateurs.,

Par exemple, si je veux compter toutes les cellules qui contiennent une date postérieure à septembre 02, 2015, je peux utiliser la formule:

=NB.SI($A$2:$A$11,”>”&DATE(2015,9,2))

de Même, vous pouvez également compter le nombre de cellules avant une date spécifiée. Si vous souhaitez inclure une date dans le comptage, l’utilisation et la « égal » de l’opérateur ainsi que « plus de/moins de » opérateur.

Vous pouvez également utiliser une référence de cellule contenant une date., Dans ce cas, vous devez combiner l’opérateur (entre guillemets doubles) avec la date à l’aide d’une esperluette (&).

Voir l’exemple ci-dessous:

=NB.SI($A$2:$A$11,”>”&F3)

#3 Compter les Cellules avec de Multiples Critères – Entre Deux Dates

Pour obtenir un nombre de valeurs entre deux valeurs, nous avons besoin d’utiliser plusieurs critères dans le NB.SI fonction.

Nous pouvons le faire en utilisant deux méthodes – une seule fonction COUNTIFS ou deux fonctions COUNTIF.,

méthode 1: Utilisation de la fonction COUNTIFS

la fonction COUNTIFS peut prendre plusieurs critères comme arguments et ne compte les cellules que lorsque tous les critères sont vrais. Pour compter les cellules avec des valeurs comprises entre deux dates spécifiées (dire le 2 septembre et le 7 septembre), on peut utiliser les éléments suivants COUNTIFS fonction:

=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2),$A$2:$A$11,”<« &DATE(2015,9,7))

La formule ci-dessus n’est pas de compter les cellules qui contiennent les dates spécifiées., Si vous souhaitez également inclure ces dates, utilisez des opérateurs supérieurs à (>=) et inférieurs à (<=). Voici la formule:

=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))

Vous pouvez aussi avoir les dates dans une cellule et l’utilisation de la cellule de référence comme critère. Dans ce cas, vous ne pouvez pas avoir l’opérateur avec la date dans les cellules., Vous devez ajouter manuellement les opérateurs dans la formule (entre guillemets) et d’ajouter une référence de cellule à l’aide d’une esperluette (&). Voir l’image ci-dessous:

=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<« &G3)

MÉTHODE 2: Utilisation de NB.SI fonctions

Si vous disposez de plusieurs critères, vous pouvez soit utiliser un COUNTIFS de fonction ou de créer une combinaison des deux NB.SI fonctions., La formule ci-dessous devraient aussi faire l’affaire:

=NB.SI($A$2:$A$11,”>”&DATE(2015,9,2))-NB.SI($A$2:$A$11,”>”&DATE(2015,9,7))

Dans la formule ci-dessus, nous trouvons d’abord le nombre de cellules qui ont une date après le 2 septembre et on soustrait le nombre de cellules avec des dates après le 7 septembre. Cela nous donnerait le résultat comme 7 (qui est le nombre de cellules qui ont des dates après Septembre 2 et sur ou avant septembre 7).,>DATE(2015,9,2))-NB.SI($A$2:$A$11,”>”&DATE(2015,9,7))

Si vous souhaitez exclure de la date de comptage, utilisez la formule suivante:

=NB.SI($A$2:$A$11,”>”&DATE(2015,9,2))-NB.SI($A$2:$A$11,”>”&DATE(2015,9,7)-NB.SI($A$2:$A$11,DATE(2015,9,7)))

en outre, vous pouvez avoir les critères de dates dans les cellules et d’utiliser les cellules de références (avec les opérateurs dans des guillemets doubles joints en utilisant esperluette).,

utilisation de caractères génériques dans les critères dans COUNTIF & fonctions COUNTIFS

Il existe trois caractères génériques dans Excel:

Vous pouvez utiliser la fonction COUNTIF avec des caractères génériques pour compter les cellules lorsque d’autres fonctions de comptage intégrées échouent. Par exemple, supposons que vous ayez un ensemble de données comme indiqué ci-dessous:

prenons maintenant divers exemples:

#1 compter les cellules contenant du texte

pour compter les cellules contenant du texte, nous pouvons utiliser le caractère générique * (astérisque)., Étant donné que l’astérisque représente un nombre quelconque de caractères, il compterait toutes les cellules contenant du texte. Voici la formule:

=COUNTIFS (C C 2 2:$C C 11,”*”)

Remarque: La formule ci-dessus ignore les cellules qui contiennent des nombres, des cellules vides et des valeurs logiques, mais compterait les cellules contenant une apostrophe (et par conséquent apparaissent vides) ou les cellules qui contiennent une chaîne vide (=””)

Voici un tutoriel détaillé sur la gestion des cas où il y a une chaîne ou une apostrophe vide.,

Voici un tutoriel détaillé sur la gestion des cas où il y a des chaînes vides ou des apostrophes.

Vous trouverez ci-dessous une vidéo qui explique différents scénarios de comptage de cellules contenant du texte.

#2 compter les cellules non vides

Si vous envisagez d’utiliser la fonction COUNTA, détrompez-vous.

essayez-le et cela pourrait vous échouer. COUNTA comptera également une cellule contenant une chaîne vide (souvent renvoyée par des formules comme =”” ou lorsque les gens n’entrent qu’une apostrophe dans une cellule). Les cellules qui contiennent des chaînes vides semblent vides mais ne le sont pas, et sont donc comptées par la fonction COUNTA.,

COUNTA comptera également une cellule contenant une chaîne vide (souvent renvoyée par des formules comme =”” ou lorsque les gens n’entrent qu’une apostrophe dans une cellule). Les cellules qui contiennent des chaînes vides semblent vides mais ne le sont pas, et sont donc comptées par la fonction COUNTA.

Donc, si vous utilisez la formule =NBVAL(A1:A11), il renvoie 11, alors qu’il devrait revenir 10.

Voici le correctif:

=COUNTIF (A A A 1:? a 1 11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ESTLOGIQUE($A$1:$A$11))

il faut bien comprendre cette formule en la décomposant:

NB.SI($N$8:$N$18,”?,*”) – Cette partie de la formule renvoie 5. Cela inclut toute cellule qui contient un caractère de texte. Un ? représente un caractère et * représente un nombre quelconque de caractères. Par conséquent, la combinaison des deux ?* dans les critères oblige excel à compter les cellules contenant au moins un caractère de texte.
COUNT (A A 1 1:$A A 11) – cela compte toutes les cellules qui contiennent des nombres. Dans l’exemple ci-dessus, il renvoie 3.
SUMPRODUCT(–ISLOGICAL (A A$1:$A A 11) – cela compte toutes les cellules qui contiennent des valeurs logiques. Dans l’exemple ci-dessus, il renvoie 2.,

#3 compter les cellules qui contiennent du texte spécifique

disons que nous voulons compter toutes les cellules où le nom du représentant commercial commence par J. cela peut facilement être réalisé en utilisant un caractère générique dans la fonction COUNTIF. Voici la formule:

=COUNTIFS($C 2 2:$C C 11,”J*”)

Le critère J* spécifie que le texte dans une cellule doit commencer par J et peut contenir n’importe quel nombre de caractères.

Si vous voulez compter les cellules qui contiennent l’alphabet n’importe où dans le texte, le flanc d’un astérisque sur les deux côtés., Par exemple, si vous souhaitez compter les cellules contenant l’alphabet « a”, utilisez *a* comme critère.

Madame Lelica