Utiliser NB.SI.ENS avec une logique OU pour plusieurs critères sous Excel

Généralement, vous pouvez utiliser la fonction NB.SI.ENS pour compter des cellules selon une ou plusieurs conditions combinées avec une logique **ET** sous Excel. Avez-vous déjà dû compter plusieurs valeurs à partir d’une même colonne ou d’une plage de cellules ? Cela revient à appliquer plusieurs conditions liées par une logique **OU**. Dans ce cas, combinez les fonctions **SOMME** et **NB.SI.ENS**, ou utilisez directement **SOMMEPROD**.

• Compter les cellules avec des conditions OU sous Excel
  • Compter les cellules avec des conditions OU à l’aide des fonctions SOMME et NB.SI.ENS
  • Compter les cellules avec des conditions OU à l’aide de la fonction SOMMEPROD
• Compter les cellules avec plusieurs ensembles de conditions OU sous Excel
  • Compter les cellules avec deux ensembles de conditions OU à l’aide des fonctions SOMME et NB.SI.ENS
  • Compter les cellules avec plusieurs ensembles de conditions OU à l’aide de la fonction SOMMEPROD

Compter les cellules avec des conditions OU sous Excel

Par exemple, j’ai une plage de données comme illustrée ci-dessous. Je souhaite maintenant compter le nombre de produits dont le nom est « Crayon » ou « Règle ». Je vais donc vous présenter deux formules efficaces pour accomplir cette tâche sous Excel.

Compter les cellules avec des conditions OU à l’aide des fonctions SOMME et NB.SI.ENS

Sous Excel, pour compter avec plusieurs conditions OU, combinez les fonctions SOMME et NB.SI.ENS à l’aide d’une constante matricielle. La syntaxe générique est la suivante :

• range: La plage Plage de données contient les critères à partir desquels vous comptez les cellules ;
• critère1, critère2, critère3… : Les conditions selon lesquelles vous souhaitez compter les cellules.

Pour compter le nombre de produits « Crayon » ou « Règle », veuillez copier ou saisir la formule ci-dessous dans une cellule vide, puis appuyez sur la touche Entréepour obtenir le résultat :

Explication de la formule :

=SUM(COUNTIFS(B2:B13,{«Pencil»,«Ruler»}))

• {«Crayon»,«Règle»}: Commencez par regrouper toutes les conditions dans une constante matricielle, comme ceci : {«Crayon»,«Règle»}, en séparant chaque élément par une virgule.
• NB.SI.ENS(B2:B13;{«Crayon»,«Règle»}) : Cette fonction NB.SI.ENS renvoie les décomptes individuels pour « Crayon » et « Règle », soit le résultat suivant : {2;3}.
• SOMME(NB.SI.ENS(B2:B13;{«Crayon»,«Règle»})) = SOMME({2,3}) : Enfin, la fonction SOMME additionne tous les éléments du tableau et renvoie le résultat : 5.

Conseils : Vous pouvez également utiliser des références de cellules pour définir vos critères. Dans ce cas, appliquez la formule matricielle ci-dessous, puis appuyez simultanément sur Ctrl + Maj + Entrée pour obtenir le résultat correct :

Compter les cellules avec des conditions OU à l’aide de la fonction SOMMEPROD

Voici une autre formule, conçue à l’aide de la fonction SOMMEPROD, qui permet également de compter des cellules selon une logique OU. Sa syntaxe générique est la suivante :

• range: La plage Plage de données contient les critères à partir desquels vous comptez les cellules ;
• criterion1, criterion2, criterion3…: Les conditions selon lesquelles vous souhaitez compter les cellules.

Veuillez copier ou saisir la formule suivante dans une cellule vide, puis appuyer sur la touche Entréepour obtenir le résultat :

Explication de la formule :

=SUMPRODUCT(1*(B2:B13={«Pencil»,«Ruler»}))

• B2:B13={«Crayon»,«Règle»} : Cette formule compare chaque élément de la plage B2:B13 aux critères « Crayon » et « Règle ». Pour chaque cellule, elle renvoie VRAI si l’un des critères est trouvé, sinon FAUX. Le résultat obtenu est le suivant : {VRAI;FAUX;FAUX;FAUX;FAUX;FAUX;VRAI;FAUX;FAUX;FAUX;VRAI;FAUX;FAUX;FAUX;FAUX;FAUX;VRAI;FAUX;FAUX;FAUX;FAUX;FAUX;VRAI;FAUX}.
• 1*(B2:B13={«Crayon»,«Règle»}) : La multiplication transforme les valeurs logiques – VRAI et FAUX – en 1 et 0, produisant ainsi un résultat tel que : {1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}.
• SOMMEPROD(1*(B2:B13={«Crayon»,«Règle»})) = SOMMEPROD({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 sous Excel

Parfois, vous devrez compter des cellules répondant à deux ensembles (ou plus) de conditions OU. Dans ce cas, vous pouvez soit combiner SOMME et NB.SI.ENS avec une constante matricielle, soit utiliser SOMMEPROD associé aux fonctions ESTNUM et EQUIV.

Compter les cellules avec deux ensembles de conditions OU à l’aide des fonctions SOMME et NB.SI.ENS

Pour traiter uniquement deux ensembles de critères OU, il suffit d’ajouter une autre constante matricielle à la formule NB.SI.ENS.

Par exemple, j’ai la plage de données illustrée ci-dessous. Je souhaite maintenant compter les personnes ayant commandé un « Crayon » ou une « Règle » et dont le montant est exactement de 200.

Veuillez saisir ou copier la formule suivante dans une cellule, puis appuyer sur la touche Entréepour obtenir le résultat (voir capture d’écran) :

 : dans la formule, utilisez un point-virgule pour la deuxième constante matricielle afin de créer un tableau vertical.

Compter les cellules avec plusieurs ensembles de conditions OU à l’aide de la fonction SOMMEPROD

La formule ci-dessus ne fonctionne que pour deux ensembles de critères OU. Si vous avez besoin de compter avec davantage de critères, une formule plus élaborée combinant SOMMEPROD, ESTNUM et EQUIV peut vous aider.

Prenons l’exemple des données ci-dessous : pour compter les personnes ayant commandé un « Crayon » ou une « Règle », dont le statut est « Livré » ou « En transit », et signé par « Bob » ou « Eko », vous devez utiliser une formule plus avancée.

Veuillez copier ou saisir la formule ci-dessous dans une cellule vide, puis appuyer sur la touche Entréepour obtenir le résultat (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;{«Pencil»;«Ruler»};0)) :

• EQUIV(B2:B13;{«Crayon»,«Règle»};0) : La fonction EQUIV compare ici chaque cellule de la plage B2:B13 à la constante matricielle correspondante. En cas de correspondance, elle renvoie la position relative de la valeur dans le tableau ; sinon, une erreur s’affiche. Vous obtenez ainsi la liste suivante : {1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}.
• ESTNUM(EQUIV(B2:B13;{«Crayon»,«Règle»};0)) = ESTNUM({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}) : La fonction ESTNUM transforme les nombres en VRAI et les erreurs en FAUX, ce qui donne : {VRAI;FAUX;FAUX;VRAI;FAUX;VRAI;FAUX;VRAI;VRAI;FAUX;VRAI;FAUX}.

La logique ci-dessus peut également être appliquée aux deuxième et troisième expressions ISNUMBER.

SOMMEPROD(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 entre eux au sein de la fonction SOMMEPROD, qui convertit automatiquement les valeurs VRAI et FAUX en 1 et 0 lors de l’opération mathématique, comme suit : SOMMEPROD({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 additionne tous les nombres du tableau pour obtenir le résultat : 3.

Fonction relative utilisée :

Autres articles :