Ne faites pas confiance à la fonction COUNTA d'Excel pour compter les cellules non vides : voici une meilleure méthode

Dans la plupart des scénarios de comptage dans Excel, COUNTBLANK compte de manière fiable les cellules vides et COUNTA compte de manière fiable les cellules remplies. Cependant, ces fonctions entraînent un paradoxe problématique : une chaîne vide provenant d'une formule est considérée comme « vide » par COUNTBLANK mais comme « remplie » par COUNTA.

Tout d'abord, je vais vous expliquer le fonctionnement de chaque fonction, puis je vais vous montrer comment j'utilise la fonction SUMPRODUCT avec LEN pour surmonter ce problème.

COUNTBLANK : Comptage des cellules de longueur nulle

La fonction COUNTBLANK compte toutes les cellules ne contenant aucun caractère. Cela comprend :

• Des cellules vraiment vides

• Cellules contenant des chaînes vides («  ») provenant de formules

Ainsi, il ne compte pas les cellules contenant :

COUNTBLANK en action

Considérons la plage A2:A6, où la cellule A2 contient une lettre, A3 contient un nombre, A4 est totalement vide, A5 contient un seul espace et A6 contient le résultat d'une formule qui renvoie une chaîne vide.

Lorsque vous appliquez la fonction COUNTBLANK à cette plage, seules les cellules contenant zéro caractère (A4 et A6) sont comptées. Même si la cellule A6 contient une formule, elle produit un résultat de zéro caractère, donc COUNTBLANK l'inclut. Et même si la cellule A5 regarde vide, il contient un espace d'un caractère, donc COUNTBLANK l'ignore.

=COUNTBLANK(A2:A6)

COUNTA : Comptage des cellules non vides

D'un autre côté, COUNTA, la fonction de comptage la plus inclusive d'Excel, capture les cellules qui ne sont pas complètement vides, telles que celles contenant :

• Nombres

• Texte

• Erreurs

• Valeurs booléennes

• Espaces

• Chaînes vides des formules

Notez la dernière puce de la liste ci-dessus : même si une formule renvoie une chaîne vide, COUNTA la compte car la cellule contient une formule. Ce comportement crée un conflit avec COUNTBLANK, car ils comptent tous deux les chaînes vides des formules.

COUNTA en action

Évaluons la même plage que précédemment, où la cellule A2 contient une lettre, A3 contient un nombre, A4 est totalement vide, A5 contient un seul espace et A6 contient le résultat d'une formule qui renvoie une chaîne vide.

En appliquant la fonction COUNTA à cette plage, le résultat est 4 car elle compte les cellules A2 (la lettre), A3 (le nombre), A5 (l'espace) et, surtout, A6 (la chaîne vide d'une formule, même si elle renvoie zéro caractère).

=COUNTA(A2:A6)

Le conflit COUNTBLANK-COUNTA (et comment le résoudre)

C'est ici que de nombreuses personnes rencontrent souvent un problème majeur : COUNTBLANK et COUNTA comptent tous deux les chaînes vides des formules.

Cela peut être utile avec COUNTBLANK car cela signifie que toutes les cellules contenant zéro caractère sont comptées, ce qui est idéal dans la plupart des scénarios. Cependant, avec COUNTA, cela pose problème car la cellule est considérée comme occupée (puisqu'elle contient une formule), même s'il n'y a aucune donnée visible. Vous pourriez vous retrouver avec un total gonflé parce que vous comptez les résultats de formules invisibles comme s’il s’agissait de points de données réels.

C'est pourquoi j'utilise une méthode alternative pour compter les cellules non vides.

Le correctif : utilisez SUMPRODUCT avec LEN pour compter les cellules en fonction de leurs caractères

De nombreux didacticiels en ligne proposent des moyens de résoudre le problème du comptage par COUNTA des chaînes vides à partir de formules, mais aucun d'entre eux ne fonctionne comme prévu car soit ils se comportent exactement de la même manière que COUNTA, soit ils utilisent des caractères génériques, qui ne fonctionnent qu'avec des lettres et non avec des chiffres.

Mon hack consiste à utiliser la fonction SUMPRODUCT avec la fonction LEN pour compter uniquement les cellules dont la longueur de contenu est supérieure à zéro, filtrant ainsi les cellules vides de longueur nulle :

=SUMPRODUCT(--(LEN(A2:A6)>0))

Cette formule vérifie explicitement si la longueur du contenu de la cellule est supérieure à zéro. Il est garanti de ne compter que les cellules comportant au moins un caractère de contenu (y compris les chiffres, le texte et les espaces), en ignorant complètement tout résultat de longueur nulle. Par conséquent, il ne compte que le texte de la cellule A2, le nombre de la cellule A3 et l’espace de la cellule A5.

Par souci de cohérence, vous pouvez remplacer le supérieur à symbole dans la formule avec un signe égal (=) pour compter uniquement les cellules contenant exactement zéro cellule (renvoyant le même résultat que la fonction COUNTBLANK) :

=SUMPRODUCT(--(LEN(A2:A6)=0))

Résumé : Choisir la bonne fonction de comptage

Ne vous méprenez pas, je ne vous dis pas d'éviter complètement la fonction COUNTA. Cela reste le moyen le plus rapide, le plus simple et le meilleur de compter les cellules lorsque vous êtes absolument sûr que votre plage de données ne contient aucune formule renvoyant une chaîne vide.

Cependant, comprendre la différence entre COUNTBLANK, COUNTA et le correctif SUMPRODUCT (LEN) signifie que vous pouvez compter les cellules avec une plus grande précision.

Voici un résumé de la raison pour laquelle vous devez utiliser chaque fonction :

---

Excel fournit une boîte à outils complète de fonctions de comptage au-delà de COUNTBLANK et COUNTA pour pratiquement tous les scénarios de comptage. COUNT compte uniquement les cellules contenant des nombres, COUNTIF compte les cellules dans une plage qui répondent à une seule condition spécifiée et COUNTIFS compte les cellules dans une plage qui répondent à plusieurs critères en même temps.