Fonction COUNTIFS, comment utiliser la fonction de comptage de cellules selon plusieurs conditions dans Excel

La fonction COUNTIFS dans Excel a de nombreuses applications pratiques. Voici ce que vous devez savoir sur l'utilisation de la fonction COUNTIFS dans Excel .

Microsoft Excel est un tableur célèbre dans le monde doté de nombreuses fonctionnalités utiles. L’avènement des logiciels rend la saisie de données et le calcul d’une série de nombres plus simples que jamais. Et lorsque vous utilisez Excel, vous devez absolument connaître la fonction COUNTIFS .

La fonction COUNTIFS dans Excel est utilisée pour compter les cellules qui satisfont à de nombreuses conditions données. La fonction COUNTIFS est l'une des fonctions statistiques Excel les plus utilisées dans Excel. Il s'agit d'une fonction avancée de la fonction COUNTIF qui compte uniquement les cellules avec une condition donnée. Lorsqu'ils travaillent avec la fonction COUNTIFS, les utilisateurs peuvent facilement trouver les cellules de résultat qui satisfont aux conditions données dans la requête. Les conditions peuvent être des nombres, des dates, du texte ou des cellules contenant des données. L'article ci-dessous vous expliquera comment utiliser la fonction COUNTIFS dans Excel.

• Comment comparer les données sur 2 colonnes Excel
• Comment utiliser la fonction DSUM pour calculer des sommes avec des conditions complexes dans Excel
• Comment utiliser la fonction SUMIF pour calculer des sommes conditionnelles dans Excel
• Utilisation de la fonction RECHERCHEV et exemples spécifiques

Instructions pour utiliser la fonction COUNTIFS dans Excel

La syntaxe de la fonction COUNTIFS est =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…) .

Là-dedans :

• Criteria_range1 est la première plage de sélection qui nécessite des statistiques, une valeur obligatoire.
• Criteria1 est une condition appliquée à la sélection criteria_range1, la valeur requise peut être une cellule, une expression ou un texte.
• [criteria_range2, criteria2] sont des paires de sélection et de condition supplémentaires, permettant jusqu'à 127 paires de sélection et de condition.

Remarque lors de l'utilisation de la fonction COUNTIFS :

• Les sélections supplémentaires doivent avoir le même nombre de lignes et de colonnes que la zone critères_range1 et peuvent être séparées les unes des autres.
• Si la condition de sélection fait référence à une cellule vide, la fonction COUNTIFS considère automatiquement la valeur comme 0.
• Les caractères ? peuvent être utilisés. Pour remplacer un certain caractère, le signe * remplace une chaîne entière de caractères. Si vous avez besoin de trouver un signe ? ou un vrai signe *, tapez ~ devant ce caractère.
• Notez que la fonction COUNTIFS n'est pas sensible à la casse.
• En général, les valeurs de texte doivent être placées entre guillemets, mais pas les nombres. Cependant, lorsqu'un opérateur logique est inclus avec un nombre, le nombre et l'opérateur doivent être mis entre guillemets comme indiqué ci-dessous :

=COUNTIFS(A1:A10,100) // count equal to 100
=COUNTIFS(A1:A10,">50") // count greater than 50
=COUNTIFS(A1:A10,"jim") // count equal to "jim"

Remarque : Les conditions supplémentaires doivent suivre les mêmes règles.

• Lors de l'utilisation d'une valeur provenant d'une autre cellule dans une condition, la référence de cellule doit être concaténée avec un opérateur lors de son utilisation. Dans l'exemple ci-dessous, COUNTIFS comptera les valeurs dans A1:A10 qui sont inférieures à la valeur de la cellule B1. Notez que l'opérateur inférieur à (qui est du texte) est placé entre guillemets, mais la référence à la cellule n'est pas nécessaire :

=COUNTIFS(A1:A10,"<"&b1) count="" cells="" less="" than="">

Remarque : COUNTIFS est l'une des nombreuses fonctions qui divisent les conditions en deux parties : Plage + critères. Cela provoque des incohérences avec d'autres formules et fonctions.

• COUNTIFS peut compter les cellules vides ou non vides. Les formules ci-dessous comptent les cellules vides et non vides dans la plage A1:A10 :

=COUNTIFS(A1:A10,"<>") // not blank
=COUNTIFS(A1:A10,"") // blank

• Le moyen le plus simple d'utiliser COUNTIFS avec des dates consiste à faire référence à une date valide dans une autre cellule contenant une référence de cellule. Par exemple, pour compter les cellules de A1:A10 qui contiennent une date supérieure à une date de B1, vous pouvez utiliser une formule comme celle-ci :

=COUNTIFS(A1:A10, ">"&B1) // count dates greater than A1

Exemple d'utilisation de la fonction COUNTIFS dans Excel

1. Fiche technique n°1

Nous avons le tableau de données ci-dessous pour faire quelques requêtes au tableau.

Exemple 1 : Calculez le nombre d'employés masculins avec 25 jours ouvrables.

Dans la cellule où nous devons saisir le résultat, saisissez la formule =COUNTIFS(C2:C7,"Male",D2:D7,25) et appuyez sur Entrée.

Là-dedans :

• C2:C7 est une zone obligatoire à 1 compte avec le sexe de l'employé.
• Le sud est l’état de la zone de comptage 1.
• C2:C7 est la 2ème zone de comptage avec la journée de travail de l'employé.
• 25 est la condition de la région du chef 2.

Résultat, nous avons 2 salariés, Nam, avec 25 jours ouvrés.

Exemple 2 : Calculez le nombre d'employés masculins dont le jour de congé est 0.

Dans la zone de saisie de la formule, saisissez =COUNTIFS(C2:C7,"Nam",E2:E7,0) et appuyez sur Entrée.

Les résultats montrent 1 salarié de sexe masculin avec 0 jour de congé.

Exemple 3 : Calculez le nombre d'employés de sexe masculin qui ont 25 jours de travail et ont des jours de congé inférieurs ou égaux à 2 jours.

Nous entrons la formule =COUNTIFS(C2:C7,"Male",D2:D7,25,E2:E7,"<> puis appuyons sur Entrée.

En conséquence, il y a 2 salariés de sexe masculin qui satisfont aux exigences d'avoir des jours de congé inférieurs ou égaux à 2 jours.

2. Fiche technique numéro 2

Exemple 1 : Calculez le total des produits taïwanais dont le prix unitaire est inférieur à 200 000 VND.

Dans la zone de saisie du résultat, nous entrons la formule de calcul =COUNTIFS(B2:B7,"*Taiwan",C2:C7,"<> puis appuyons sur Entrée.

Le résultat a été 2 produits taïwanais répondant aux exigences.

Exemple 2 : Calculez le total des produits taïwanais avec des prix unitaires compris entre 100 000 VND et 150 000 VND.

Dans la zone de saisie du résultat, saisissez la formule =COUNTIFS(B2:B7,"*Taiwan",C2:C7,">100000",C2:C7,"<> puis appuyez sur Entrée.

En conséquence, il y aura 2 produits taïwanais dont le prix sera compris entre 100 000 VND et 150 000 VND.

Comment utiliser la fonction COUNTIFS combinée avec SUM dans Excel

La fonction COUNTIFS est essentiellement une version plus complexe de la fonction COUNTIF. Le principal avantage de COUNTIFS par rapport à COUNTIF est qu'il prend en charge plusieurs conditions et plages.

Cependant, vous pouvez également définir une plage et une condition unique pour la fonction COUNTIFS, tout comme vous l'avez fait avec la fonction COUNTIF.

Une chose importante que vous devez comprendre à propos de la fonction COUNTIFS avant de l'utiliser est qu'elle fait plus que simplement additionner les résultats des cellules qui répondent aux critères de chaque plage de cellules.

En effet, si vous avez deux conditions pour deux plages, les cellules de la première plage sont filtrées deux fois : une fois via la première condition, puis via la seconde condition. Cela signifie que la fonction COUTNIFS ne renverra que les valeurs qui remplissent les deux conditions, dans leur plage donnée.

Vous pouvez mieux comprendre la fonctionnalité de la fonction COUNTIFS en étudiant l'exemple ci-dessous.

Exemple de la fonction COUNITFS dans Excel

En plus de la couleur des pommes, il y a aussi une colonne décrivant leur taille. Le but ultime de cet exemple est de compter le nombre de grosses pommes rouges.

1. Sélectionnez la cellule dans laquelle vous souhaitez afficher les résultats. (Dans cet exemple, l'article affichera le grand nombre de pommes rouges dans la cellule E3).

2. Accédez à la barre de formule et saisissez la formule ci-dessous :

=COUNTIFS(A3:A11, "Red", B3:B11, "Big")

3. Avec cette commande, la formule vérifie les cellules A3 à A11 pour la condition « Rouge ». Les cellules qui réussissent ce test sont ensuite testées à nouveau dans la plage B3 à B11 pour la condition « Big » .

4. Appuyez sur le bouton Entrée.

5. Désormais, Excel comptera le nombre de grosses pommes rouges.

Exemples de résultats de la fonction Excel COUNTIFS

Observez comment la formule compte les cellules qui possèdent à la fois les attributs rouge et grand. La formule prend les cellules A3 à A11 et les teste pour trouver le résultat rouge. Les cellules qui satisfont à cette condition sont ensuite testées à nouveau par rapport à la condition suivante dans la deuxième plage, dans ce cas, la grande condition.

En conclusion, les plages et conditions après la première plage et condition rétrécissent de plus en plus le filtre de comptage et ne sont pas indépendantes les unes des autres. Ainsi, le résultat final de la recette est des pommes rouges et grosses. Vous pouvez compter le nombre de pommes rouges ou grosses en combinant la fonction COUNTIF avec la fonction SUM .

1. Sélectionnez la cellule dans laquelle vous souhaitez afficher les résultats de la formule. (Dans cet exemple, l'article utilisera la cellule E6).

2. Entrez la formule ci-dessous :

=SUM(COUNTIF(A3:A11, "Red"), COUNTIF(B3:B11, "Big"))

3. Cette formule comptera les cellules contenant des pommes rouges, puis le nombre de cellules contenant de grosses pommes, et enfin, elle fera la somme de ces deux nombres.

4. Appuyez sur le bouton Entrée.

5. Excel comptera et affichera désormais le nombre de pommes grosses ou rouges.

Combinez la fonction COUNTIF avec la fonction SUM

Ci-dessus se trouvent 2 tableaux de données utilisant la fonction COUNTIFS dans Excel. Les utilisateurs doivent écrire les conditions accompagnant la zone de données de condition pour que la fonction COUNTIFS les détermine correctement.

Comment corriger les erreurs courantes lors de l'utilisation de la fonction COUNTIFS dans Excel

COUNTIFS ne fonctionne pas lors du comptage des valeurs de texte

Lors du comptage des chaînes de texte, elles doivent être insérées entre guillemets. Sinon, la fonction COUNTIFS ne pourra pas compter la chaîne de texte et renverra la valeur 0. Dans l'exemple d'image suivant, la chaîne de texte n'a pas été placée entre guillemets. Par conséquent, cette formule renvoie 0.

Pour corriger cette erreur, il vous suffit de réécrire exactement la formule : =COUNTIFS(E5:E12, "Car")

COUNTIFS ne fonctionne pas car la référence de plage est erronée

Lorsque vous utilisez plusieurs critères dans la fonction COUNTIFS, la plage de cellules pour les autres critères doit avoir le même nombre de cellules. Sinon, la fonction COUNTIFS ne fonctionnera pas.

Disons que dans cet exemple, nous voulons compter les ventes de voitures à Austin. La formule saisie est =COUNTIFS(E5:E12,"Car",D5:D11,"Austin"). En regardant attentivement la formule, vous verrez que la première plage de critères ici est E5:E12 mais que la deuxième plage de critères est D5:D11. Le nombre de cellules dans la plage pour ce critère n'est pas le même.

Maintenant, appuyez sur Entrée , la formule renverra la #VALEUR ! .

Réécrivez correctement la formule comme suit : =COUNTIFS(E5:E12,"Car",D5:D12,"Austin")

COUNTIFS ne fonctionne pas à cause d'une erreur dans la formule

Si la formule n'est pas insérée correctement, la fonction COUNTIFS ne fonctionnera pas. Lorsque vous utilisez un opérateur mathématique, tel que supérieur à (>), inférieur à (<), égal à="" (=")" et "not="" equals=""> <> ) , l' opérateur et le le critère numérique doit être entré dans la même équation. Par exemple, si vous souhaitez rechercher des ventes supérieures à 100 000 $, vous devez insérer la formule suivante :

=COUNTIFS(F5:F12,">" 100000)

Ici, seul l'opérateur est inséré à l'intérieur de l'équation, il n'y a pas de critère numérique.

Si vous appuyez sur Entrée, la boîte de dialogue du message Microsoft Excel affichera : « Il y a un problème avec cette formule ».

Pour résoudre le problème. Tapez la formule exacte :

=COUNTIFS(F5:F12,">100000")

Nous avons maintenant entré l'opérateur et les critères entre parenthèses. Maintenant, cette formule renverra la quantité requise.

Appuyez sur Entrée.

En conséquence, vous recevrez un montant de vente supérieur à 100 000 USD.

Je vous souhaite du succès !