COUNTIF() Excel : Compter les cellules en fonction des conditions

Toutes les façons d'utiliser la fonction COUNTIF() dans Excel

Maintenant que vous savez comment fonctionne la fonction COUNTIF() dans Excel, examinons d'autres exemples d'utilisation.

Comptage des cellules contenant du texte

COUNTIF() peut compter le nombre de fois qu'un texte spécifique apparaît dans les données. Par exemple, j'ai une liste de fruits dans A2:A10 et je veux savoir combien de fois les pommes apparaissent. Pour cela, je m'inscris :

=COUNTIF(A2:A10, "Apple")

Cette formule comptera toutes les cellules de cette plage qui correspondent exactement à Pomme.

Comptez les cellules contenant du texte à l'aide de COUNTIF(). Image par l'auteur.

Compter des valeurs numériques

Nous pouvons également compter les nombres supérieurs, inférieurs ou égaux à une valeur spécifique. Par exemple, j'ai un ensemble de données et je peux maintenant trouver différentes choses à partir de celui-ci avec la formule COUNTIF().  

• Compter combien de fruits ont une quantité supérieure à 500: =COUNTIF(B2:B10,">500")

• Compter le nombre de fruits dont la quantité n'est pas égale à 500: =COUNTIF(B2:B10, "<>500")

• Trouver combien de fruits sont exactement 500 en quantité : =COUNTIF(B2:B10, “500”)

• Comptez le nombre de fruits dont la quantité est inférieure à 500: =COUNTIF(B2:B10,"<500")

Comptez les valeurs numériques à l'aide de la fonction COUNTIF(). Image par l'auteur.

Utilisation de caractères génériques pour les correspondances partielles

Parfois, nous voulons compter les cellules qui contiennent une partie spécifique d'un mot ou d'une phrase. Dans ce cas, nous utilisons des caractères génériques. Il existe deux types de caractères génériques :  

• * correspond à n'importe quel nombre de caractères.

• ? ne correspond qu'à un seul caractère. 

Par exemple, j'ai un ensemble de données sur les fruits et j'utilise deux formules différentes pour vous montrer comment COUNTIF() gère les cas de caractères génériques. Pour compter tous les fruits qui contiennent n n'importe où dans la liste des fruits, j'utilise la formule suivante :

=COUNTIF(A2:A10, "*n*")

Cette formule utilise le caractère de remplacement astérisque *, qui représente n'importe quel nombre de caractères (y compris zéro). En tapant "*n*", Excel recherche les cellules où "n" apparaît n'importe où dans le texte - au début, au milieu ou à la fin. La formule compte toutes les cellules correspondantes dans la plage A2:A10 et renvoie le nombre total de fruits contenant la lettre "n".

Pour compter tous les fruits à quatre lettres commençant par K, je tape :

=COUNTIF(A2:A10,"K???")

Cette formule utilise le caractère de remplacement ?, qui représente exactement un caractère. En tapant "K???", Excel compte toutes les cellules de la plage A2:A10 qui commencent par la lettre K et qui ont exactement quatre lettres, et affiche le résultat final.

Utilisez des caractères génériques dans la fonction COUNTIF(). Image par l'auteur.

Application de critères à partir d'une autre cellule

Au lieu de coder en dur les conditions, nous pouvons même utiliser la référence de la cellule. Cela rend la formule plus flexible car elle se met à jour automatiquement lorsque la valeur de la cellule change.

Si je veux savoir combien de fruits ont une quantité supérieure à 300je tape la condition dans la cellule D2 puis j'utilise la référence de cette cellule dans la formule comme suit :

=COUNTIF(B2:B11, D2)

Nous pouvons également écrire l'opérateur logique entre guillemets à l'intérieur de la formule et utiliser & pour référencer la cellule D3 comme suit :

=COUNTIF(B2:B11, ">"&D3)

Comptez les cellules en utilisant la référence de la cellule dans la fonction COUNTIF(). Image par l'auteur.

Comptage des cellules vierges et non vierges

Les données manquantes peuvent être tout aussi importantes que les données dont vous disposez. Le site COUNTIF() nous aide à repérer ces lacunes. Supposons que je gère des stocks et que je veuille savoir combien de fruits sont en rupture de stock (cellules vides). Pour cela, je crée la formule suivante :

=COUNTIF(B2:B11,"")

Et pour compter le nombre de fruits disponibles (cellules non vides), j'utilise la formule suivante :

=COUNTIF(B2:B11, "<>")

Cependant, si votre cellule contient du texte invisible, comme un espace ou un caractère spécial, COUNTIF() les comptera comme des cellules non vides.

Comptez les cellules vides et non vides à l'aide de la fonction COUNTIF(). Image par l'auteur.

Compter les dates en fonction de différentes conditions   

Nous pouvons également compter les dates en fonction de différentes conditions. Supposons que nous voulions compter le nombre d'employés embauchés à la date indiquée dans la cellule B11 (1er mars 2024). Notre formule serait donc la suivante :

=COUNTIF(B2:B9, B11)

Et pour vérifier qui a été embauché au plus tard à la date indiquée dans la cellule B11, j'utilise la formule suivante :

=COUNTIF(B2:B9, ">="&B11)

Comptez la différence entre les dates à l'aide de la fonction COUNTIF(). Image par l'auteur.

COUNTIF() avec plusieurs critères 

Nous pouvons également utiliser COUNTIF() avec plusieurs critères. Si je veux compter combien de dates tombent entre le 1er janvier 2024 et le 30 janvier 2024, je tape :

=COUNTIF(B2:B9, ">="&B12) - COUNTIF(B2:B9, ">"&B13)

Vous pouvez également utiliser la fonction COUNTIFS() pour plusieurs critères, comme suit :

=COUNTIFS(B2:B9,">="&B12,B2:B9,"<="&B13)

Les deux donnent le même résultat. 

Utilisez COUNTIF() pour deux conditions. Image par l'auteur.

Excel COUNTIF() vs COUNTIFS()

La fonction COUNTIF() d'Excel compte les cellules en fonction d'un seul critère, comme nous l'avons vu dans les exemples ci-dessus, et nous pouvons même filtrer plusieurs conditions en utilisant plusieurs COUNTIF(), comme nous l'avons vu dans le dernier exemple.

C'est le moment de dire que, si vos données nécessitent plusieurs critères, Excel propose une fonction étendue et plus récente appelée COUNTIFS(). Si COUNTIFS() se comporte exactement comme COUNTIF() lorsqu'une seule condition est spécifiée, il vous permet également d'appliquer plus d'une condition.

Bien que COUNTIFS() soit plus récent et mieux adapté aux conditions de AND, COUNTIF() a toujours sa place dans Excel. Il crée souvent des formules plus lisibles pour des conditions simples, ce qui rend vos feuilles de calcul plus faciles à comprendre et à gérer. Plus important encore, contrairement à COUNTIFS(), COUNTIF() gère les conditions de OR, où vous souhaitez compter les cellules qui répondent à l'un ou l'autre de plusieurs critères.

Comprenons-le à l'aide d'un exemple simple : 

=COUNTIF(B2:B11, "100") + COUNTIF(B2:B11, "900")

Cette formule comptera les cellules où la valeur est soit 100 soit 900. Comme il n' y a pas de 900 dans les cellules et qu'il n'y a qu'un seul 100, lerésultat sera 1 .

Application de la logique OR à l'aide de COUNTIF(). Image par l'auteur. 

Supposons que je doive également savoir combien de fois une valeur comporte 300 et kiwi dans la même ligne. Pour cela, je peux utiliser COUNTIFS():

=COUNTIFS(B1:B11, "300", A1:A11, "kiwi")

La formule comptera les lignes où les deux conditions sont remplies. Cependant, il donne 0 parce qu'aucune des conditions n'a été remplie. 

Application de la logique AND à l'aide de COUNTIFS(). Image par l'auteur. 

Cette distinction est utile à connaître, c'est pourquoi nous l'avons incluse dans notre aide-mémoire sur les formules Excel. Et si vous voulez vraiment rester au courant des derniers développements, je vous recommande de suivre notre cursus de compétences " Excel Fundamentals".

Erreurs courantes et dépannage

Même les meilleurs utilisateurs d'Excel rencontrent parfois des problèmes avec COUNTIF(). La fonction est utile, mais elle présente quelques bizarreries qui peuvent vous faire trébucher. Voici les problèmes les plus courants auxquels les gens sont confrontés et comment les résoudre rapidement.

Comptage incorrect pour les longues chaînes de texte

COUNTIF() ne prend pas en charge les chaînes de texte de plus de 255 caractères. Si vous travaillez avec de longs morceaux de texte et que vous obtenez des résultats inattendus, essayez d'utiliser CONCATENATE() ou l'opérateur & pour décomposer et combiner vos critères sous une forme gérable : 

=COUNTIF(range, CONCATENATE("First part of text", "Second part of text"))

Ou

=COUNTIF(range, "First part of text" & "Second part of text")

Erreur VALUE ! dans COUNTIF()

Une erreur #VALUE! dans COUNTIF() se produit souvent lorsque la formule fait référence à un classeur fermé. Cela se produit parce qu'Excel ne permet pas à COUNTIF() de compter les données d'un fichier externe à moins que le fichier ne soit ouvert. Pour résoudre ce problème, assurez-vous que le classeur référencé est ouvert avant d'exécuter la formule. L'erreur devrait disparaître une fois le fichier ouvert et COUNTIF() fonctionnera correctement.

Utilisation incorrecte des opérateurs

Il arrive que l'on oublie de mettre les opérateurs logiques entre guillemets. Assurez-vous toujours que les opérateurs tels que >, <, et <> sont entre guillemets dans vos critères. Sinon, vous n'obtiendrez pas le résultat.

 Par exemple, la formule ci-dessous affichera une erreur :

=COUNTIF(B2:B10,>500)

Pour éviter cela, veillez à ce que l'opérateur logique soit placé entre guillemets. 

=COUNTIF(B2:B10,">500")

Meilleures pratiques pour Excel COUNTIF()

Voici quelques pratiques clés à suivre lorsque vous travaillez avec COUNTIF() dans Excel. 

• Utilisez des plages nommées: Au lieu de sélectionner une plage comme A2:A10 dans votre formule, donnez un nom personnalisé à la plage pour faciliter la compréhension de la formule. Par exemple, pour compter combien de fois Banana apparaît dans une liste, nous utiliserons =COUNTIF(A2:A10, "Banana"). Mais si je nomme la plage Items et que j'utilise la référence de la cellule pour la condition, je peux mettre à jour la formule comme suit : =COUNTIF(Items, C2). La formule est ainsi plus claire et plus facile à comprendre. 

• Utilisez COUNTIFS() pour des conditions multiples : Il arrive que nous devions compter des données en fonction de plusieurs conditions. Dans ce cas, au lieu d'utiliser plusieurs formules COUNTIF(), utilisez COUNTIFS() car c'est le meilleur choix pour appliquer plusieurs filtres.

COUNTIF() vs. Fonctions connexes

En dehors de COUNTIF() et COUNTIFS(), nous disposons de nombreuses autres fonctions Excel pour compter les cellules. Cependant, chaque fonction a un objectif spécifique. Examinons-les de plus près :

Comme beaucoup d'autres fonctions Excel, COUNTIF() est également l'un des moyens les plus utiles pour compter et filtrer les données. Au lieu de parcourir manuellement votre liste, vous pouvez utiliser cette fonction pour trouver instantanément ce dont vous avez besoin. De plus, il fonctionne avec du texte, des nombres, des cellules vides et des modèles, ce qui facilite l'analyse des données. 

Si vous souhaitez en savoir plus, commencez par notre cours d'introduction à Excel pour apprendre l'essentiel. Pour une base solide, le cursus de compétences Excel Fundamentals vous permettra d'aborder tous les concepts de base, étape par étape. Enfin, si vous travaillez avec des données financières, notre cours Modélisation financière dans Excel vous guidera dans l'élaboration de modèles financiers et de prévisions professionnels.