Countifs avec logique OU pour plusieurs critères dans Excel
Normalement, vous pouvez utiliser la fonction COUNTIFS pour compter les cellules en fonction d'une ou plusieurs conditions avec la logique AND dans Excel. Avez-vous déjà été confronté à une situation où vous devez compter plus d'une valeur à partir d'une seule colonne ou d'une plage de cellules ? Cela signifie compter avec plusieurs conditions et logique OU. Dans ce cas, vous pouvez appliquer les fonctions SUM et COUNTIFS ensemble, ou utiliser la fonction SUMPRODUCT.
- Compter les cellules avec des conditions OU dans Excel
- Compter les cellules avec plusieurs ensembles de conditions OU dans Excel
Compter les cellules avec des conditions OU dans Excel
Par exemple, j'ai une plage de données comme illustré ci-dessous, maintenant, je veux compter le numéro du produit qui est "Crayon" ou "Règle", ici, je vais discuter de deux formules pour résoudre cette tâche dans Excel.
Compter les cellules avec des conditions OR en utilisant les fonctions SUM et COUNTIFS
Dans Excel, pour compter avec plusieurs conditions OR, vous pouvez utiliser les fonctions SUM et COUNTIFS avec une constante de tableau, la syntaxe générique est :
- range: la plage de données contient les critères à partir desquels vous comptez les cellules ;
- criterion1, criterion2, criterion3…: les conditions sur lesquelles vous souhaitez compter les cellules.
Pour compter le nombre de produits qui sont « Crayon » ou « Règle », veuillez copier ou saisir la formule ci-dessous dans une cellule vide, puis appuyez sur Entrer clé pour obtenir le résultat:
Explication de la formule:
=SUM(COUNTIFS(B2:B13,{"Crayon","Règle"}))
- {"Crayon","Règle"}: Tout d'abord, vous devez regrouper toutes les conditions dans une constante de tableau comme celle-ci : {"Crayon","Règle"}, séparez les éléments par des virgules.
- COUNTIFS(B2:B13,{"Crayon","Règle"}): Cette fonction COUNTIFS obtiendra des comptes individuels pour « Crayon » et « Règle », et vous obtiendrez le résultat suivant : {2,3}.
- SOMME(COUNTIFS(B2:B13,{"Crayon","Règle"}))=SOMME({2,3}): Enfin, cette fonction SUM additionne tous les éléments du tableau et renvoie le résultat : 5.
Pourboires: Vous pouvez également utiliser des références de cellules pour les critères, veuillez appliquer la formule matricielle ci-dessous, puis appuyez sur Ctrl + Maj + Entrée touches ensemble pour obtenir le résultat correct :
Compter les cellules avec des conditions OU en utilisant la fonction SOMMEPROD
Voici une autre formule créée par la fonction SOMMEPROD qui peut également aider à compter les cellules avec la logique OU. La syntaxe générique est :
- range: la plage de données contient les critères à partir desquels vous comptez les cellules ;
- criterion1, criterion2, criterion3…: les conditions sur lesquelles vous souhaitez compter les cellules.
Veuillez copier ou saisir la formule suivante dans une cellule vide et appuyez sur Entrer touche pour retourner le résultat :
Explication de la formule:
=SUMPRODUCT(1*(B2:B13={"Crayon","Règle"}))
- B2:B13="{"Crayon","Règle"}: Cette expression compare chaque critère « Crayon », « Règle » avec la cellule de plage B2:B13. Si le critère est rempli, il renvoie un VRAI, sinon, un FAUX s'affichera, vous obtiendrez le résultat comme ceci : {VRAI,FAUX;FAUX,FAUX;FAUX,FAUX;FAUX,VRAI;FAUX,FAUX;VRAI,FAUX; ;FAUX,FAUX;FAUX,VRAI;FAUX,FAUX;FAUX,FAUX;FAUX,VRAI;FAUX,FAUX}.
- 1*(B2:B13={"Crayon","Règle"}): La multiplication convertit les valeurs logiques - TRUE et FALSE en 1 et 0, ainsi elle obtiendra le résultat comme ceci : {1,0;0,0;0,0;0,1;0,0;1,0;0,0 ,0,1;0,0;0,0;0,1;0,0;XNUMX}.
- SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): Enfin, la fonction SOMMEPROD additionne tous les nombres du tableau pour obtenir le résultat : 5.
Compter les cellules avec plusieurs ensembles de conditions OU dans Excel
Parfois, vous devrez peut-être compter les cellules avec deux ou plusieurs ensembles de conditions OU, dans ce cas, vous pouvez utiliser SUM et COUNTIFS avec une constante matricielle ou SUMPRODUCT avec les fonctions ISNUMBER MATCH.
Compter les cellules avec deux ensembles de conditions OU en utilisant les fonctions SUM et COUNTIFS
Pour traiter seulement deux ensembles de critères OU, il vous suffit d'ajouter une autre constante matricielle à la formule COUNTIFS.
Par exemple, j'ai la plage de données illustrée ci-dessous, maintenant, je veux compter les personnes qui ont commandé "Crayon" ou "Règle" et le montant est soit <100, soit >200.
Veuillez entrer ou copier la formule suivante dans une cellule, puis appuyez sur Entrer clé pour obtenir le résultat, voir capture d'écran:
Notes: Dans la formule, vous devez utiliser un point-virgule pour la deuxième constante de tableau, ce qui crée un tableau vertical.
Compter les cellules avec plusieurs ensembles de conditions OU en utilisant la fonction SOMMEPROD
La formule ci-dessus ne fonctionne que pour deux ensembles de critères OU, si vous devez compter avec plus de critères, une formule SUMPRODUCT complexe avec des fonctions ISNUMBER MATCH peut vous aider.
Prenez les données ci-dessous par exemple, pour compter les personnes qui ont commandé "Crayon" ou "Règle" et le Statut est soit "Livré" soit "En transit" et signé par "Bob" ou "Eko", vous devez appliquer une formule complexe .
Veuillez copier ou saisir la formule ci-dessous dans une cellule vide, puis appuyez sur Entrer pour obtenir le calcul, voir capture d'écran :
Explication de la formule:
=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))
ISNUMBER(MATCH(B2:B13,{"Crayon","Règle"},0)) :
- MATCH(B2:B13,{"Crayon","Règle"},0): Cette fonction MATCH est utilisée pour comparer chaque cellule de la plage B2:B13 avec la constante de tableau correspondante. Si la correspondance est trouvée, elle renvoie une position relative de la valeur dans le tableau, sinon une valeur d'erreur affichée. Ainsi, vous obtiendrez la liste des tableaux comme ceci : {1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;# N / A}.
- ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}): La fonction ISNUMBER convertit les nombres en VRAI et les valeurs d'erreur en FAUX comme ceci : {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE}.
La logique ci-dessus peut également être appliquée aux deuxième et troisième expressions ISNUMBER.
SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0))):
- Ensuite, ces trois tableaux sont multipliés ensemble dans SUMPRODUCT, qui convertit automatiquement les valeurs VRAI et FAUX en 1 et 0 dans le cadre de l'opération mathématique comme ceci : SUMPRODUCT({1;0;0;1;0;1;0;1; 1;0;1;0}*{1;1;0;0;1;1;0;1;1;0;1;1} *{1;0;0;0;0;0;0; 1;0;0;1;0})=SOMMEPROD({1;0;0;0;0;0;0;1;0;0;1;0}).
- Enfin, la fonction SOMMEPROD fait la somme de tous les nombres du tableau pour obtenir le résultat : 3.
Fonction relative utilisée:
- SOMME:
- La fonction Excel SUM renvoie la somme des valeurs fournies.
- COUNTIF :
- La fonction NB.SI est une fonction statistique dans Excel qui est utilisée pour compter le nombre de cellules qui répondent à un critère.
- SOMMEPRODUIT :
- La fonction SOMMEPROD peut être utilisée pour multiplier deux ou plusieurs colonnes ou tableaux ensemble, puis obtenir la somme des produits.
- ISNUMBER :
- La fonction Excel ISNUMBER renvoie TRUE lorsqu'une cellule contient un nombre et FALSE sinon.
- CORRESPONDRE:
- La fonction MATCH Microsoft Excel recherche une valeur spécifique dans une plage de cellules et renvoie la position relative de cette valeur.
Plus d'articles:
- Compter des valeurs numériques uniques en fonction de critères
- Dans la feuille de calcul Excel, vous pouvez rencontrer un problème qui compte le nombre de valeurs numériques uniques en fonction d'une condition spécifique. Par exemple, comment puis-je compter les valeurs de quantité uniques du produit « T-shirt » à partir du rapport, comme illustré ci-dessous ? Dans cet article, je vais montrer quelques formules pour réaliser cette tâche dans Excel.
- Compter les cellules qui correspondent à deux critères ou plus
- Dans Excel, pour compter le nombre de cellules en fonction de deux ou plusieurs critères, la fonction COUNTIFS peut vous aider à résoudre cette tâche rapidement et facilement.
- Compter le nombre de lignes avec plusieurs critères OU
- Pour compter le nombre de lignes avec plusieurs critères dans différentes colonnes, avec la logique OU, la fonction SOMMEPROD peut vous aider. Par exemple, j'ai un rapport de produit comme illustré ci-dessous, maintenant, je veux compter les lignes où le produit est "T-shirt" ou la couleur est "Noir". Comment gérer cette tâche dans Excel?
Les meilleurs outils de productivité de bureau
Kutools for Excel - vous aide à vous démarquer de la foule
Kutools pour Excel possède plus de 300 fonctionnalités, Assurez-vous que ce dont vous avez besoin est à portée de clic...
Onglet Office - Activer la lecture et l'édition par onglets dans Microsoft Office (y compris Excel)
- Une seconde pour basculer entre des dizaines de documents ouverts!
- Réduisez chaque jour des centaines de clics de souris, dites adieu à la souris.
- Augmente votre productivité de 50% lors de l'affichage et de l'édition de plusieurs documents.
- Apporte des onglets efficaces à Office (y compris Excel), tout comme Chrome, Edge et Firefox.
