Comment utiliser plusieurs conditions dans Excel COUNTIF et COUNTIFS

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

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

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

Introduction aux fonctions Excel NB.SI et NB.SI.

Voyons d'abord comment utiliser les fonctions NB.SI et NB.SI dans Excel.

Fonction Excel NB.SI (avec un seul critère)

La fonction Excel NB.SI fonctionne mieux lorsque vous souhaitez compter des cellules en fonction d'un seul critère.Si vous souhaitez compter en fonction de plusieurs critères, utilisez la fonction NB.SI.

syntaxe

=NB.SI(plage, condition)

Paramètres d'entrée

• gamme -  La plage de cellules que vous souhaitez calculer.
• état -Critères qui doivent être évalués par rapport à la plage de cellules à compter.

Fonction Excel COUNTIFS (avec plusieurs critères)

La fonction Excel COUNTIFS fonctionne mieux lorsque vous souhaitez compter des cellules en fonction de plusieurs critères.

syntaxe

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

Paramètres d'entrée

• critères_plage1 – La plage de cellules que vous souhaitez évaluer par rapport au critère 1.
• critères1 – Le critère que vous souhaitez évaluer est critère_plage1 pour déterminer les cellules à évaluer.
• [critères_range2] – La plage de cellules que vous souhaitez évaluer par rapport au critère 2.
• [critère2] – Le critère que vous souhaitez évaluer est critère_plage2 pour déterminer les cellules à compter.

Examinons maintenant quelques exemples d'utilisation de plusieurs critères dans la fonction NB.SI d'Excel.

Utilisez la condition NUMBER dans la fonction Excel COUNTIF

#1 Compter les cellules lorsque le critère est égal à une valeur

Pour obtenir le nombre de cellules où le paramètre de condition est égal à la valeur spécifiée, vous pouvez saisir la condition directement ou utiliser une référence de cellule qui contient la condition.

Vous trouverez ci-dessous un exemple dans lequel nous comptons les cellules contenant le chiffre 9 (ce qui signifie que le paramètre de condition est égal à 9).Voici la formule :

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

Dans l'exemple ci-dessus (dans l'image), le critère est dans la cellule D3.Vous pouvez également entrer des conditions directement dans les formules.Par exemple, vous pouvez également utiliser :

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

#2 Compter les cellules lorsque la condition est supérieure à la 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 pouvons l'utiliser directement dans les formules ou nous pouvons utiliser des références de cellule avec des conditions.

Chaque fois que nous utilisons un opérateur dans une condition dans Excel, nous devons le mettre entre guillemets doubles.Par exemple, si la condition est supérieure à 10, nous devons entrer "> 10" comme condition (voir image ci-dessous) :

Voici la formule :

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

Vous pouvez également inclure des conditions dans les cellules et utiliser des références de cellule comme conditions.Dans ce cas, vous n'avez pas besoin de mettre la condition entre guillemets :

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

Cela peut également être le cas lorsque vous souhaitez qu'une condition soit dans une cellule mais que vous ne souhaitez pas l'utiliser avec un opérateur.Par exemple, vous pouvez souhaiter que le nombre dans la cellule D3 soit 10 au lieu de >10.

Dans ce cas, vous devez créer un paramètre conditionnel, qui est une combinaison d'opérateur et de référence de cellule (voir l'image ci-dessous) :

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

Remarque : Lorsque vous combinez des opérateurs et des références de cellule, les opérateurs sont toujours placés entre guillemets doubles.Les opérateurs et les références de cellule sont reliés 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 pouvons l'utiliser directement dans les formules ou nous pouvons utiliser des références de cellule avec des conditions.

Chaque fois que nous utilisons un opérateur dans une condition dans Excel, nous devons le mettre entre guillemets doubles.Par exemple, si le critère est que la quantité doit être inférieure à 5, alors nous devons saisir "<5" comme critère (voir image ci-dessous) :

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

Vous pouvez également inclure des conditions dans les cellules et utiliser des références de cellule comme conditions.Dans ce cas, vous n'avez pas besoin de mettre la norme entre guillemets doubles (voir image ci-dessous) :

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

En outre, vous souhaiterez peut-être que la condition soit dans la cellule, mais pas avec l'opérateur.Par exemple, vous souhaiterez peut-être que la cellule D3 contienne le chiffre 5 au lieu de <5.

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

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

Remarque : Lorsque vous combinez des opérateurs et des références de cellule, les opérateurs sont toujours placés entre guillemets doubles.Les opérateurs et les références de cellule sont reliés par une esperluette (&).

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

Pour obtenir le nombre de valeurs entre deux valeurs, nous devons utiliser plusieurs conditions dans la fonction NB.SI.

Voici deux façons de procéder :

Méthode XNUMX : Utiliser la fonction NB.SI.SI

La fonction NB.SI.SI peut gérer plusieurs conditions en tant qu'arguments et compter les cellules uniquement si toutes les conditions sont VRAIES.Pour compter les cellules dont les valeurs sont comprises entre deux valeurs spécifiées, telles que 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 contenant 5 ou 10.Si vous souhaitez inclure ces cellules, utilisez les opérateurs supérieur ou égal à (>=) et inférieur ou égal à (<=).Voici la formule :

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

Vous pouvez également inclure ces conditions dans des cellules et utiliser des références de cellules comme conditions.Dans ce cas, vous n'avez pas besoin de mettre la norme entre guillemets doubles (voir image ci-dessous) :

Vous pouvez également utiliser une combinaison de références de cellules et d'opérateurs (saisie d'opérateurs directement dans les formules).Lors de la combinaison d'opérateurs et de références de cellule, les opérateurs sont toujours placés entre guillemets doubles.Les opérateurs et les références de cellule sont reliés par une esperluette (&).

Méthode 2 : Utiliser deux fonctions NB.SI

Si vous avez plusieurs conditions, vous pouvez utiliser COUNTIFS ou créer une combinaison de fonctions COUNTIF.La formule suivante fera 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 avec une valeur supérieure à 5, puis soustrayons le nombre de cellules avec une valeur supérieure à 10.Cela nous donnera un résultat de 5 (c'est-à-dire avec des valeurs supérieures à 5 et inférieures ou égales à 10).

Si vous souhaitez que la formule contienne à 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 "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 inclure ces conditions dans des cellules et utiliser des références de cellule, ou vous pouvez utiliser une combinaison d'opérateurs et de références de cellule.

Utiliser les conditions TEXTE dans les fonctions Excel

#1 Compter les cellules lorsque la condition est égale au texte spécifié

Pour compter les cellules qui contiennent une correspondance exacte avec un texte spécifié, nous pouvons simplement utiliser ce texte comme condition.Par exemple, dans un jeu de données (illustré ci-dessous), si je voulais compter toutes les cellules nommées Joe, je pourrais utiliser la formule suivante :

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

Puisqu'il s'agit d'une chaîne de texte, je dois mettre la condition de texte entre guillemets doubles.

Vous pouvez également inclure une condition dans une cellule, puis utiliser cette référence de cellule (comme indiqué ci-dessous) :

=NB.SI($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 la condition ou la plage de conditions.Assurez-vous de nettoyer les données avant d'utiliser ces formules.

#2 Compter les cellules lorsque la condition n'est pas égale au 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 non égal (<>).

En supposant que vous souhaitiez compter toutes les cellules qui ne contiennent pas le nom JOE, voici une formule qui peut le faire :

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

Vous pouvez également inclure des conditions dans les cellules et utiliser des références de cellule comme conditions.Dans ce cas, vous n'avez pas besoin de mettre la norme entre guillemets doubles (voir image ci-dessous) :

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

Cela peut également être le cas lorsque vous souhaitez qu'une condition soit dans une cellule mais que vous ne souhaitez pas l'utiliser avec un opérateur.Par exemple, vous pouvez souhaiter que la cellule D3 porte le nom Joe au lieu de <>Joe.

Dans ce cas, vous devez créer un paramètre conditionnel, qui est une combinaison d'opérateur et de référence de cellule (voir l'image ci-dessous) :

=COUNTIF($B$2:$B$11,”<>”&E3)

Lors de la combinaison d'opérateurs et de références de cellule, les opérateurs sont toujours placés entre guillemets doubles.Les opérateurs et les références de cellule sont reliés par une esperluette (&).

Utilisation des conditions DATE dans les fonctions Excel COUNTIF et COUNTIFS

Excel stocke les dates et les heures sous forme de nombres.Nous pouvons donc l'utiliser comme un nombre.

#1 Compter les cellules lorsque la condition est égale à la date spécifiée

Pour obtenir le nombre de cellules contenant une date spécifiée, nous utiliserons l'opérateur égal (=) avec la date.

Pour travailler avec des dates, je recommande d'utiliser la fonction DATE, car elle élimine tout risque que la valeur de la date soit erronée.Ainsi, par exemple, si je voulais utiliser la date du 2015er septembre 9, je pourrais utiliser la fonction DATE comme ceci :

= DATE (2015,9,1)

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

Voici la formule pour compter le nombre de cellules contenant la date 02/09/2015 :

=COUNTIF($A$2 :$A$11,DATE(2015,9,2))

#2 Compter les cellules lorsque la condition est antérieure ou postérieure à la date spécifiée

Pour compter les cellules qui contiennent des dates avant ou après une date spécifiée, nous pouvons utiliser les opérateurs inférieur à/supérieur à.

Par exemple, si je voulais compter toutes les cellules contenant des dates après le 2015 septembre 9, je pourrais utiliser la formule suivante :

=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 des dates dans le décompte, utilisez l'opérateur "égal à" avec les opérateurs "supérieur à/inférieur à".

Vous pouvez également utiliser des références de cellules contenant des dates.Dans ce cas, vous devez utiliser une esperluette (&) pour combiner l'opérateur (entre guillemets doubles) avec la date.

Voir l'exemple ci-dessous :

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

#3 Compter les cellules avec plusieurs critères – entre deux dates

Pour obtenir le nombre de valeurs entre deux valeurs, nous devons utiliser plusieurs conditions dans la fonction NB.SI.

Nous pouvons le faire de deux manières - une seule fonction COUNTIFS ou deux fonctions COUNTIF.

Méthode XNUMX : Utiliser la fonction NB.SI.SI

La fonction NB.SI.SI peut accepter plusieurs conditions comme arguments et compter les cellules uniquement si toutes les conditions sont VRAIES.Pour compter les cellules avec des valeurs entre deux dates spécifiées, comme le 9 septembre et le 2 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 la date spécifiée.Si vous souhaitez également inclure ces dates, utilisez les opérateurs supérieur ou égal à (>=) et inférieur ou égal à (<=).Voici la formule :

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

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

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

Méthode 2 : Utiliser la fonction NB.SI

Si vous avez plusieurs conditions, vous pouvez utiliser une fonction NB.SI ou créer une combinaison de deux fonctions NB.SI.La formule suivante fera é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 dont la date est postérieure au 9 septembre, puis soustrayons le nombre de cellules dont la date est postérieure au 2 septembre.Cela donnera un résultat de 9 (c'est-à-dire le nombre de cellules avec des dates après le 7 septembre et le ou avant le 7 septembre).

Si vous ne souhaitez pas que la formule calcule à la fois le 9 septembre et le 2 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 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,日期(2015,9,7)))

De plus, vous pouvez définir des dates standard dans les cellules et utiliser des références de cellule (et des opérateurs entre guillemets doubles avec esperluette).

Utilisation de caractères génériques dans les conditions des fonctions NB.SI et NB.SI.

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

1. *(Astérisque) - il représente n'importe quel nombre de caractères.Par exemple, ex* peut signifier excel, excels, exemple, expert, etc.
2. ? (point d'interrogation) - Il représente un seul caractère.Par exemple, Trömp pourrait signifier Trump ou Clochard.
3. ~ (tilde) - Utilisé pour identifier les caractères génériques (~, *, ?) dans le texte.

Vous pouvez utiliser la fonction NB.SI 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 disposiez d'un ensemble de données qui ressemble à ceci :

Prenons maintenant quelques 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 les astérisques représentent n'importe quel nombre de caractères, il comptera toutes les cellules contenant du texte.Voici la formule :

=NB.SI($C$2:$C$11,"*")

Remarque : La formule ci-dessus ignore les cellules qui contiennent des nombres, les cellules vides et les valeurs logiques, mais compte les cellules qui contiennent des apostrophes (et donc apparaissent vides) ou qui peuvent avoir été incluses dans la formule.

Voici un tutoriel détaillé sur la gestion des cas avec des chaînes vides ou des apostrophes.

#2 Compter les cellules non vides

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

Essayez-le, il pourrait vous décevoir.COUNTA comptera également les cellules qui contiennent des chaînes vides (généralement renvoyées par des formules comme ="", ou lorsque les gens entrent simplement des apostrophes dans les cellules).Les cellules qui contiennent des chaînes vides semblent être vides mais ne le sont pas, elles sont donc comptées par la fonction NBVAL.

COUNTA comptera également les cellules qui contiennent des chaînes vides (généralement renvoyées par des formules comme ="", ou lorsque les gens entrent simplement des apostrophes dans les cellules).Les cellules qui contiennent des chaînes vides semblent être vides mais ne le sont pas, elles sont donc comptées par la fonction NBVAL.

Donc, si vous utilisez la formule = NB (A1:A11), il renvoie 11 alors qu'il 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 :

#3 Compter les cellules contenant un texte spécifique

Supposons que nous voulions compter toutes les cellules où le nom du représentant commercial commence par J.Ceci peut être facilement réalisé en utilisant des caractères génériques dans la fonction NB.SI.Voici la formule :

=NB.SI($C$2:$C$11,"J*") 

La condition J* spécifie que le texte de la cellule doit commencer par J et peut contenir n'importe quel nombre de caractères.

Si vous souhaitez compter les cellules contenant des lettres n'importe où dans le texte, entourez-les d'un astérisque.Par exemple, si vous souhaitez compter les cellules contenant la lettre "a", utilisez *a* comme condition.

Comparé à mes autres articles, celui-ci est anormalement long.J'espère que vous aimez.Dites-moi ce que vous en pensez en laissant un commentaire.