Compter les cellules contenant soit x, soit y dans une plage Excel

Pour compter les cellules contenant l’une ou l’autre de deux valeurs, certains utilisateurs Excel combinent deux fonctions NB.SI. Cependant, cette approche suit en réalité une logique ET — elle compte les cellules contenant à la fois x et y — plutôt qu’une logique OU, qui vise à inclure les cellules contenant soit x, soit y. Pour éviter tout double comptage et obtenir un résultat précis, ce tutoriel vous explique pas à pas comment utiliser une formule basée sur la fonction SOMMEPROD afin de dénombrer efficacement, dans une plage donnée, les cellules contenant soit x, soit y sous Excel.

Comment compter les cellules contenant soit x, soit y dans une plage Excel

Comme le montre la capture d’écran ci-dessous, la plage de données B3:B9 contient des valeurs. Pour compter les cellules qui incluent soit « KTE », soit « KTO », utilisez la formule suivante.

Formule générique

=SUMPRODUCT(--((ISNUMBER(FIND("criteria1",rng)) + ISNUMBER(FIND("criteria2",rng)))>0))

Arguments

Comment utiliser cette formule ?

1. Sélectionnez une cellule vide pour y afficher le résultat.

2. Saisissez la formule ci-dessous dans cette cellule, puis appuyez sur la touche Entrée pour obtenir le résultat.

=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>0))

Comment ces formules fonctionnent-elles ?

=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>0))

• 1. FIND(D3,B3:B9) : La fonction TROUVE vérifie si la valeur « KTE » située en D3 est présente dans la plage B3:B9 et renvoie le tableau suivant : {1,#VALEUR!,#VALEUR!,#VALEUR!,#VALEUR!,1,#VALEUR!}.
  Dans ce tableau, les deux occurrences de 1 indiquent que la première et l’avant-dernière cellule de la plage B3:B9 contiennent la valeur « KTE », tandis que les #VALEUR! signifient que « KTE » n’a pas été trouvé dans les autres cellules.
• 2. ISNUMBER{1,#VALUE!, #VALUE!, #VALUE!, #VALUE!,1, #VALUE!}La fonction ESTNUMÉRIQUE renvoie VRAI lorsqu’elle détecte un nombre dans le tableau et FAUX lorsqu’elle rencontre une erreur, produisant ainsi le tableau suivant : {VRAI;FAUX;FAUX;FAUX;FAUX;VRAI;FAUX}.
• 3. FIND(D4,B3:B9)La fonction TROUVE vérifie également si la valeur « KTO » située en D4 est présente dans la plage limitée (B3:B9) et renvoie le tableau suivant : {#VALEUR!; 1; #VALEUR!; #VALEUR!; #VALEUR!; #VALEUR!; #VALEUR!}.
• 4. ISNUMBER{#VALUE!,1,#VALUE!,#VALUE!, #VALUE!,#VALUE!,#VALUE!}La fonction ESTNUMÉRIQUE renvoie VRAI lorsqu’elle détecte un nombre dans le tableau et FAUX lorsqu’elle rencontre une erreur, produisant ainsi le tableau suivant : {FAUX;VRAI;FAUX;FAUX;FAUX;FAUX;FAUX}.
• 5. {TURE;FALSE;FALSE;FALSE;FALSE;TURE;FALSE} + {FALSE;TURE;FALSE;FALSE;FALSE;FALSE;FALSE}Ici, les deux tableaux renvoient le résultat suivant : {1;1;0;0;0;1;0}.
• 6. {1;1;0;0;0;1;0}>0Ici, on compare chaque nombre du tableau pour vérifier s’il est supérieur à 0, ce qui donne le résultat suivant : {VRAI;VRAI;FAUX;FAUX;FAUX;VRAI;FAUX}.
• 7. --({TURE;TRUE;FALSE;FALSE;FALSE;TURE;FALSE})Ces deux signes moins transforment « VRAI » en 1 et « FAUX » en 0, vous donnant ainsi un nouveau tableau : {1;1;0;0;0;1;0}.
• 8. =SUMPRODUCT({1;1;0;0;0;1;0})La fonction SOMMEPROD additionne tous les nombres du tableau et renvoie le résultat final, qui est 3 dans ce cas.

Fonctions associées

Formules associées