La fonction COUNTIFS d’Excel est utilisée pour obtenir le nombre de cellules dans une ou plusieurs plages qui répondent à un ensemble de conditions ou de critères spécifiés.
Il peut arriver que la fonction COUNTIFS ne fonctionne pas comme prévu et qu’elle renvoie une erreur #VALUE ! , 0, amessage indiquant qu’il y a une erreur dans la formule ou une autre valeur inattendue.
Voici quelques-unes des raisons pour lesquelles la fonction ne fonctionne pas comme prévu dans Microsoft Excel :
- Lorsqu’une formule fait référence à une plage d’un classeur qui est fermé.
- Utilisation d’une valeur provenant d’une autre cellule sans la concaténer à l’opérateur logique utilisé.
- Ne pas insérer les critères textuels entre guillemets ( » »).
- Les plages suivantes n’ont pas le même nombre de colonnes et de lignes que la première plage de critères.
- Les critères numériques et l’opérateur logique ne sont pas insérés entre guillemets ( » »)
- Essayer d’obtenir un nombre en utilisant la logique OR.
- Changements ultérieurs dans l’ensemble de données.
- Erreurs dans la formule.
Dans ce tutoriel, nous expliquons comment ces raisons peuvent être résolues afin que la fonction renvoie le nombre correct que nous recherchons.
1. La formule fait référence à une plage qui se trouve dans un autre classeur qui est fermé
Lorsque la fonction COUNTIFS fait référence à une plage qui se trouve dans un autre classeur fermé, elle renvoie une erreur #VALUE ! au lieu du nombre de cellules attendu.
Dans l’exemple suivant, au lieu que la formule renvoie le nombre de produits rouges, une erreur #VALUE ! a été générée parce que la formule fait référence à une plage dans un autre classeur fermé.her workbook called Product Colours that is closed.
Solution
La solution pour que les comptes ne fonctionnent pas dans ce scénario est d’ouvrir le classeur fermé et d’appuyer sur la touche F9 du clavier pour que la formule recalcule les paramètres corrects. Nous obtiendrons alors le compte correct, comme le montre l’exemple suivant.
2. Utilisation de la valeur d’une autre cellule sans concaténation de la référence de la cellule à l’opérateur logique
Si vous utilisez un opérateur logique et une référence de cellule comme critères pour la fonction COUNTIFS dans une formule, et que vous ne concaténéz pas la référence de cellule à l’opérateur logique en insérant & avant la référence de cellule, la fonction renverra un 0 au lieu du nombre attendu.
Ceci est illustré dans l’exemple suivant où la formule =COUNTIFS(G2:G6, »>M2″) a été utilisée.
Solution
Dans l’exemple suivant, nous devons saisir la formule correcte =COUNTIFS(G2:G6,« > » &M2) dans la cellule I3. Lorsque nous appuyons sur la touche ENTER , nous obtenons le nombre correct de 4.
Dans la formule correcte, seul l’opérateur logique plus grand que (>) est entre guillemets ( » »), et l’opérateur de concaténation & est placé avant la référence de la cellule M2.
3. Ne pas mettre de guillemets ( » ») autour des critères textuels
Si les critères textuels ne sont pas insérés entre guillemets, la fonction COUNTIFS renverra une valeur 0 au lieu du nombre correct que nous attendons, comme le montre l’exemple ci-dessous.
La formule de la cellule J2 est =COUNTIFS(C2:C6,Rouge). La formule renvoie 0 car le critère Rouge n’a pas été mis entre guillemets ( » »).
Solution au problème des comptes qui ne fonctionnent pas
Nous devons mettre les critères textuels de la formule entre guillemets, comme indiqué dans l’exemple suivant, et nous obtiendrons alors le nombre correct de 2.
4. La plage de critères suivante n’a pas la même forme que la première plage de critères.
Lorsque nous utilisons plusieurs critères dans la fonction COUNTIFS et que les plages de critères optionnels supplémentaires ne correspondent pas à la première plage de critères, l’erreur #VALUE est générée.
Dans l’exemple suivant, la formule =COUNTIFS(B2:B13, « Rouge »,C2:C10, « Microsoft ») a été saisie dans la cellule G3 pour tenter d’obtenir le nombre de produits rouges fournis par Microsoft.
Lorsque nous appuyons sur la touche Entrée, nous obtenons l’erreur #VALUE ! comme indiqué ci-dessous.
En effet, la deuxième plage de critères C2:C10 n’a pas le même nombre de cellules que la première plage de critères B2:B13.
Solution
Pour corriger ce problème, nous devons nous assurer que la deuxième plage de critères est de la même taille que la première plage de critères, comme indiqué ci-dessous :
Lorsque nous appuyons sur la touche ENTER, nous obtenons le nombre correct de 2, comme indiqué ci-dessous :
5. Ne pas insérer les critères numériques et l’opérateur logique entre guillemets ( » »)
Lorsque nous utilisons des opérateurs logiques tels que égal à (=), supérieur à (>), inférieur à (<) et non égal à (<>) dans une formule et que nous ne parvenons pas à insérer les critères numériques et l’opérateur logique entre les mêmes guillemets, Excel nous envoie un message indiquant que la formule comporte une erreur.
Dans l’exemple suivant, nous n’avons pas placé l’opérateur logique et le critère numérique entre guillemets. Seul l’opérateur logique se trouve entre guillemets.
Lorsque nous appuyons sur la touche ENTER, nous obtenons le message suivant :
Solution
Nous devons nous assurer que nous incluons à la fois l’opérateur logique et le critère numérique à l’intérieur des mêmes guillemets, comme indiqué ci-dessous :
Lorsque nous appuyons sur la touche ENTER, nous obtenons le nombre correct de 5, comme indiqué ci-dessous :
6. Essayer d’obtenir un comptage à l’aide de la logique OU
Bien que la fonction COUNTIFS puisse générer des comptages corrects en utilisant la logique AND, elle ne génère qu’une valeur 0 lorsque nous essayons de l’utiliser pour calculer en utilisant la logique OR.
Dans l’exemple suivant, nous essayons d’obtenir le nombre de catégories de produits rouges et bleus en utilisant la logique OU.
Lorsque nous appuyons sur la touche ENTER, nous obtenons la valeur inattendue de 0, comme indiqué ci-dessous. Ceci est dû au fait que la fonction COUNTIFS ne peut pas calculer en utilisant la logique OR.
Solution
Nous pouvons contourner ce problème de logique OU en utilisant les fonctions COUNTIFS et SUM ensemble.
Dans l’exemple suivant, nous avons saisi la formule =SUM(COUNTIFS(B2:B13,{« Rouge », « Bleu »})) dans la cellule G3.
Lorsque nous appuyons sur la touche ENTER, nous obtenons la valeur correcte de 6, comme indiqué ci-dessous. Cela s’explique par le fait que la fonction COUNTIFS a renvoyé 3 nombres de rouges et 3 nombres de bleus à partir de la plage de critères B2:B13, et que la fonction SUM a additionné les valeurs pour obtenir 6.
7. Changements ultérieurs dans l’ensemble de données
Les modifications apportées à un ensemble de données peuvent faire en sorte que la fonction COUNTIFS ne génère pas les valeurs attendues. Par exemple, dans l’ensemble de données suivant, la fonction COUNTIF fonctionne correctement et renvoie le nombre exact de produits bleus.
Mais s’il y a des changements dans les données, par exemple dans ce cas nous ajoutons le mot couleur à Bleu, la fonction COUNTIFS affiche maintenant la valeur incorrecte de 0.
Solution
Il est possible de remédier à cette situation en utilisant des caractères génériques avec la fonction COUNTIF.
Dans l’exemple suivant, nous avons utilisé le caractère astérisque (*) dans les critères, ce qui nous permet d’obtenir le nombre correct de 4 produits. En effet, la fonction COUNTIF peut désormais rechercher des correspondances partielles dans la plage de critères.
8. Erreurs dans la formule
Il arrive que la fonction COUNTIF ne fonctionne pas en raison d’une erreur dans la formule, par exemple l’oubli d’une virgule de séparation entre la plage de critères et l’opérateur logique.
Dans l’exemple suivant, il n’y a pas de virgule entre la plage de critères D2:D13 et l’opérateur logique supérieur à (>).
Lorsque nous appuyons sur la touche ENTER, nous obtenons une boîte de message nous informant qu’il y a une erreur dans la formule.
Solution
Il convient d’être particulièrement vigilant lors de l’écriture des formules afin de s’assurer que la syntaxe est correcte. Dans l’exemple suivant, l’insertion d’une virgule entre la plage de critères et l’opérateur supérieur à (>) résout le problème et nous obtenons le nombre correct de 5.
Conclusion
COUNTIFS est une fonction très puissante qui permet de générer le nombre de cellules dans les plages de données qui répondent à des conditions spécifiques.
Dans ce tutoriel, nous avons examiné les différentes raisons pour lesquelles cette fonction ne fonctionne pas toujours comme prévu et les moyens de les résoudre.