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 des cellules en fonction de plusieurs critères, vous pouvez utiliser les fonctions COUNTIF ou COUNTIFS dans Excel.

Ce didacticiel couvre différentes manières d'utiliser un ou plusieurs critères dans les fonctions COUNTIF et COUNTIFS dans Excel.

Bien que je me concentrerai principalement sur les fonctions COUNTIF et COUNTIFS dans ce didacticiel, tous ces exemples peuvent également être utilisés dans d'autres fonctions Excel qui prennent en entrée plusieurs critères (tels que SUMIF, SUMIFS, AVERAGEIF et AVERAGEIFS).

Une introduction aux fonctions Excel COUNTIF et COUNTIFS

Examinons d'abord 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 dans lesquelles vous souhaitez compter des cellules en fonction d'un seul critère. Si vous souhaitez compter sur la base de plusieurs critères, utilisez la fonction COUNTIFS.

Syntaxe

=COUNTIF(plage, critères)

Arguments d'entrée

• gamme - la plage de cellules que vous souhaitez 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 dans lesquelles vous souhaitez compter des cellules en fonction de plusieurs critères.

Syntaxe

=COUNTIFS(critères_plage1, critères1, [critères_plage2, critères2]… )

Arguments d'entrée

• critères_plage1 - La plage de cellules pour laquelle vous souhaitez évaluer par rapport aux critères1.
• critères1 - les critères que vous souhaitez évaluer pour critère_plage1 afin de déterminer quelles cellules compter.
• [criteria_range2] - La plage de cellules pour laquelle vous souhaitez évaluer par rapport aux critères2.
• [critères2] - les critères que vous souhaitez évaluer pour critère_plage2 afin de déterminer quelles cellules compter.

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

Utilisation des critères NUMBER dans les fonctions Excel COUNTIF

#1 Compter les cellules lorsque les critères sont égaux à une valeur

Pour obtenir le nombre de cellules où l'argument critère est égal à une valeur spécifiée, vous pouvez soit entrer directement les critères, soit 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 critère est égal à 9). Voici la formule :

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

Dans l'exemple ci-dessus (sur la photo), le critère est dans la cellule D3. Vous pouvez également saisir les critères directement dans la formule. Par exemple, vous pouvez également utiliser :

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

#2 Compter les cellules lorsque le critère est SUPÉRIEUR à 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 soit l'utiliser directement dans la formule, soit utiliser une référence de cellule contenant les critères.

Chaque fois que nous utilisons un opérateur dans des critères dans Excel, nous devons le mettre entre guillemets. Par exemple, si le critère est supérieur à 10, alors nous devons entrer ">10" comme critère (voir photo ci-dessous):

Voici la formule :

=COUNTIF($B$2:$B$11,">10″)

Vous pouvez également avoir les critères dans une cellule et utiliser la référence de cellule comme critère. Dans ce cas, vous n'avez PAS besoin de mettre les critères entre guillemets :

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

Il peut également arriver que vous souhaitiez que les critères soient dans une cellule, mais que vous ne le vouliez pas avec l'opérateur. Par exemple, vous voudrez peut-être que la cellule D3 ait le numéro 10 et non >10.

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

=COUNTIF($B$2:$B$11,">"&D3)

REMARQUE : 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 joints par une esperluette (&).

#3 Compter les cellules lorsque le critère est INFÉRIEUR À une valeur

Pour obtenir le nombre de cellules avec une valeur inférieure à une valeur spécifiée, nous utilisons l'opérateur inférieur à ("<"). Nous pourrions soit l'utiliser directement dans la formule, soit utiliser une référence de cellule contenant les critères.

Chaque fois que nous utilisons un opérateur dans des critères dans Excel, nous devons le mettre entre guillemets. Par exemple, si le critère est que le nombre doit être inférieur à 5, alors nous devons entrer "<5" comme critère (voir photo ci-dessous):

=COUNTIF($B$2:$B$11,"<5″)

Vous pouvez également avoir les critères dans une cellule et utiliser la référence de cellule comme critère. Dans ce cas, vous n'avez PAS besoin de mettre les critères entre guillemets (voir photo ci-dessous):

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

En outre, il peut arriver que vous souhaitiez que les critères soient dans une cellule, mais que vous ne le vouliez pas avec l'opérateur. Par exemple, vous pouvez souhaiter que la cellule D3 ait le numéro 5 et non <5.

Dans ce cas, vous devez créer un argument de critère qui est une combinaison d'opérateur et de référence de cellule :

=COUNTIF($B$2:$B$11,"<"&D3)

REMARQUE : 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 joints par une esperluette (&).

# 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 VRAI. Pour compter les cellules dont les valeurs sont comprises entre deux valeurs spécifiées (disons 5 et 10), nous pouvons utiliser la fonction COUNTIFS suivante :

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

REMARQUE : la formule ci-dessus ne compte pas les cellules qui contiennent 5 ou 10. Si vous souhaitez inclure ces cellules, utilisez les opérateurs supérieur à égal à (>=) et inférieur à égal à (<=). Voici la formule :

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

Vous pouvez également avoir ces critères dans des cellules et utiliser la référence de cellule 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 joints par une esperluette (&).

MÉTHODE 2 : Utilisation de deux fonctions COUNTIF

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

=COUNTIF($B$2:$B$11,">5″)-COUNTIF($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 nous soustrayons le nombre de cellules avec une valeur supérieure à 10. Cela nous donnerait le résultat comme 5 (qui est le nombre de cellules qui ont valeurs supérieures à 5 et inférieures à 10).

Si vous souhaitez que la formule inclue à la fois 5 et 10, utilisez plutôt la formule suivante :

=COUNTIF($B$2:$B$11,">=5″)-COUNTIF($B$2:$B$11,">10″)

Si vous souhaitez que la formule exclue à la fois « 5 » et « 10 » du décompte, utilisez la formule suivante :

=COUNTIF($B$2:$B$11,">=5″)-COUNTIF($B$2:$B$11,">10″)-COUNTIF($B$2:$B$11,10)

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

Utilisation des critères TEXTE dans les fonctions Excel

#1 Compter les cellules lorsque les critères sont égaux à 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 (présenté ci-dessous dans la photo), si je veux compter toutes les cellules contenant le nom Joe, je peux utiliser la formule ci-dessous :

=COUNTIF($B$2:$B$11,"Joe")

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

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

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

REMARQUE : Vous pouvez obtenir des résultats erronés s'il y a des espaces de début/de 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 les critères ne sont 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 devons utiliser l'opérateur différent de ().

Supposons que vous vouliez compter toutes les cellules qui ne contiennent pas le nom JOE, voici la formule qui le fera :

=COUNTIF($B$2:$B$11,"Joe")

Vous pouvez également avoir les critères dans une cellule et utiliser la référence de cellule comme critère. Dans ce cas, vous n'avez PAS besoin de mettre les critères entre guillemets (voir photo ci-dessous):

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

Il peut également arriver que vous souhaitiez que les critères soient dans une cellule mais que vous ne le vouliez pas avec l'opérateur. Par exemple, vous pouvez souhaiter que la cellule D3 porte le nom Joe et non Joe.

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

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

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 joints par une esperluette (&).

Utilisation des critères 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 les critères sont égaux à 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 la date. Ainsi, par exemple, si je souhaite utiliser la date du 1er septembre 2015, je peux utiliser la fonction DATE comme indiqué ci-dessous :

=DATE(2015,9,1)

Cette formule reviendrait à la même date malgré les différences régionales. Par exemple, 01-09-2015 serait le 1er septembre 2015 selon la syntaxe de date des États-Unis et le 09 janvier 2015 selon la syntaxe de date du Royaume-Uni. Cependant, cette formule renverrait toujours le 1er septembre 2105.

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

=COUNTIF($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 une date antérieure ou postérieure à une date spécifiée, nous pouvons utiliser les opérateurs inférieur à/supérieur à.

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

=COUNTIF($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 décompte, utilisez et l'opérateur « égal à » avec l'opérateur « supérieur à/inférieur à ».

Vous pouvez également utiliser une référence de cellule qui contient une date. Dans ce cas, vous devez combiner l'opérateur (entre guillemets doubles) avec la date en utilisant une esperluette (&).

Voir exemple ci-dessous :

=COUNTIF($A$2:$A$11,">"&F3)

#3 Compter les cellules avec plusieurs critères - Entre deux dates

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

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 VRAI. Pour compter les cellules dont les valeurs sont comprises entre deux dates spécifiées (disons le 2 septembre et le 7 septembre), nous pouvons utiliser la fonction COUNTIFS suivante :

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

La formule ci-dessus ne compte pas les cellules contenant les dates spécifiées. Si vous souhaitez également inclure ces dates, utilisez les opérateurs supérieur à égal à (>=) et inférieur à égal à (<=). Voici la formule :

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

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

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

MÉTHODE 2 : Utilisation des fonctions COUNTIF

Si vous avez plusieurs critères, vous pouvez soit utiliser une fonction COUNTIFS, soit créer une combinaison de deux fonctions COUNTIF. La formule ci-dessous ferait également l'affaire :

=COUNTIF($A$2:$A$11,">"&DATE(2015,9,2))-COUNTIF($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 nous soustrayons 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 le 2 septembre et au plus tard le 7 septembre).

Si vous ne voulez pas que la formule compte à la fois le 2 septembre et le 7 septembre, utilisez plutôt la formule suivante :

=COUNTIF($A$2:$A$11,">="&DATE(2015,9,2))-COUNTIF($A$2:$A$11,">"&DATE(2015,9,7))

Si vous souhaitez exclure les deux dates du décompte, utilisez la formule suivante :

=COUNTIF($A$2:$A$11,">"&DATE(2015,9,2))-COUNTIF($A$2:$A$11,">"&DATE(2015,9,7)-COUNTIF($A $2:$A$11,DATE(2015,9,7)))

En outre, vous pouvez avoir les dates des critères dans les cellules et utiliser les références des cellules (ainsi que les opérateurs entre guillemets doubles joints à l'aide d'une esperluette).

Utilisation de WILDCARD CHARACTERS dans les critères des fonctions COUNTIF & COUNTIFS

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

1. * (astérisque) - Il représente un nombre quelconque de caractères. Par exemple, ex* pourrait signifier excel, excels, example, expert, etc.
2. ? (point d'interrogation) - Il représente un seul caractère. Par exemple, Tr?mp pourrait signifier Trump ou Tramp.
3. ~ (tilde) - Il est utilisé pour identifier un caractère générique (~, *, ?) dans le texte.

Vous pouvez utiliser la fonction COUNTIF avec des caractères génériques pour compter les cellules lorsqu'une autre fonction de comptage intégrée échoue. 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$2:$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 donc paraissant vides) ou les cellules contenant une chaîne vide (="") qui peuvent avoir été renvoyées comme un partie d'une formule.

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

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 qui contient une chaîne vide (souvent renvoyée par des formules comme = "" ou lorsque les gens entrent uniquement 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 qui contient une chaîne vide (souvent renvoyée par des formules comme = "" ou lorsque les gens entrent uniquement 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 =COUNTA(A1:A11), elle renvoie 11, alors qu'elle devrait renvoyer 10.

Voici le correctif :

=COUNTIF($A$1:$A$11,"?*")+COUNT($A$1:$A$11)+SUMPRODUCT(-ISLOGICAL($A$1:$A$11))

Comprenons cette formule en la décomposant :

• COUNTIF($N$8:$N$18,"?*") - Cette partie de la formule renvoie 5. Cela inclut toute cellule contenant un caractère de texte. UNE ? représente un caractère et * représente un nombre quelconque de caractères. Par conséquent, la combinaison ?* dans les critères oblige Excel à compter les cellules contenant au moins un caractère de texte.
• COUNT($A$1:$A$11) - Ceci compte toutes les cellules qui contiennent des nombres. Dans l'exemple ci-dessus, il renvoie 3.
• SUMPRODUCT(-ISLOGICAL($A$1:$A$11) - Ceci 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:$C$11,"J*")

Le critère J* spécifie que le texte d'une cellule doit commencer par J et peut contenir un nombre quelconque de caractères.

Si vous souhaitez compter les cellules qui contiennent l'alphabet n'importe où dans le texte, placez-le de côté avec un astérisque des deux côtés. Par exemple, si vous souhaitez compter les cellules contenant l'alphabet "a", utilisez *a* comme critère.

Cet article est inhabituellement long par rapport à mes autres articles. J'espère que vous l'avez apprécié. Faites-moi part de vos impressions en laissant un commentaire.

Vous pouvez également trouver les didacticiels Excel suivants utiles :

• Comptez le nombre de mots dans Excel.
• Comptez les cellules en fonction de la couleur d'arrière-plan dans Excel.
• Comment additionner une colonne dans Excel (5 méthodes vraiment simples)