Fonctions de tableau excel: comment compter les cellules non vierges sur plusieurs feuilles de travail?

J'ai un classeur Excel avec de nombreuses feuilles de travail, dont certaines contiennent une valeur numérique dans la cellule A1 :

  'DATA_1' 'DATA_2' 'DATA_3' AAA 1 1.6 1 -0.8 1 

Je veux déterminer le nombre de cellules A1 par une seule formule dans tout le classeur qui contient des données, de sorte que le résultat de l'exemple ci-dessus devrait être 2 car la cellule de la dernière feuille de calcul est vide.

Étant donné que le nombre de feuilles de travail peut varier, j'utilise une fonction définie par l'utilisateur (UDF) qui renvoie un tableau contenant les noms de toutes les feuilles:

 Function SHEET_NAMES() As Variant ' returns names of all sheets as an array Dim idx As Long, retArray() As String Application.Volatile True With ThisWorkbook.Sheets ReDim retArray(.Count) For idx = 1& To .Count retArray(idx) = .Item(idx).Name Next idx SHEET_NAMES() = retArray End With End Function 

Cette UDF fonctionne bien, la formule {=SHEET_NAMES()} renvoie le tableau attendu ( {"DATA_1","DATA_2","DATA_3"} ). (Notez que l'UDF doit être considéré comme non modifiable.)

Mon idée est maintenant d'utiliser l'UDF avec les fonctions ADDRESS() et INDIRECT() pour référencer les cellules A1 de toutes les feuilles de calcul disponibles de manière dynamique, pour utiliser ISNUMBER() pour vérifier si un numéro valide est présent, convertir le booléen FALSE / TRUE en 0 par N() et enfin, pour utiliser SUM() pour résumer tous les 0 , comme ceci:

 {=SUM(N(ISNUMBER(INDIRECT(ADDRESS(ROW(A1);COLUMN(A1);;;SHEET_NAMES())))))} 

Cependant, le résultat est toujours 0 , même si aucune feuille de calcul ne contient une cellule vide A1 .

J'ai découvert que la fonction ADDRESS() renvoie le bon réseau de chaînes, représentant les références à toutes les cellules A1 , qui est {"DATA_1!$A$1","DATA_2!$A$1","DATA_3!$A$1"} .
INDIRECT() retourne {#VALUE!,#VALUE!,#VALUE!} ISNUMBER() il ne supporte pas les tableaux apparemment, mais la fonction conteneur ISNUMBER() prend en charge les tableaux, donc cela semble faire l'itération sur les éléments du tableau correctement, et Il en résulte {TRUE,TRUE,FALSE} .
La fonction N() effectue la conversion de la manière prévue, d'où il en résulte {1,1,0} .
Mais la SUM() résulte toujours de 0 , quelle que soit la quantité de feuilles existantes et si certaines ou toutes contiennent des nombres valides dans la cellule A1 . (Tout comme sidenote: si je saisis la formule comme une fonction non-tableau, le résultat dépend uniquement de la première feuille de calcul).

J'ai essayé d'utiliser NOT(ISBLANK()) et NOT(ISERROR()) au lieu de ISNUMBER() , et j'ai essayé de changer SUM(N(ISNUMBER())) en SUM(COUNT()) , mais sans succès (tous NOT(ISBLANK()) à 0 , sauf NOT(ISBLANK()) qui donne 3 ).
(J'ai également essayé de remplacer ROW(A1) et COLUMN(A1) par ROWS($A$1:A1) et COLUMNS($A$1:A1) , car cela devrait résoudre un problème de INDIRECT() , Selon certaines pages Web, mais sans changement aussi.)

Alors, pouvez-vous me dire ce que je fais mal ici, et comment surmonter l'échec sans perdre la souplesse?

(Si possible, je préférerais une solution sans utiliser COUNTIF() ou SUMIF() ni aucune autre fonction nécessitant qu'une certaine condition soit donnée en chaîne, pour maintenir la portabilité.)

Vous n'avez pas besoin de tous les noms de feuilles, juste le premier et le dernier:

 =COUNT(Sheet1:Sheet3!A1) 

Vous pouvez essayer avec ce code:

=SUMPRODUCT(SUBTOTAL(2;INDIRECT(ADDRESS(ROW();COLUMN();;;SHEET_NAMES))))