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 des conditions OR en utilisant les fonctions SUM et COUNTIFS
  • Compter les cellules avec des conditions OU en utilisant la fonction SOMMEPROD
• Compter les cellules avec plusieurs ensembles de conditions OU dans Excel
  • Compter les cellules avec deux ensembles de conditions OU en utilisant les fonctions SUM et COUNTIFS
  • Compter les cellules avec plusieurs ensembles de conditions OU en utilisant la fonction SOMMEPROD

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.

Astuces: 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:

Plus d'articles: