Excel array functions -- how to count non-blank cells over multiple worksheets?

Question

I have an Excel workbook with numerous worksheets, some of them containing a numeric value in cell A1:

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

I want to determine the number of A1 cells by a single formula throughout the entire workbook which contain data, so the result for the above example should be 2 because the cell in the last worksheet is blank.

Since the number of worksheets may vary, I am using a user-defined function (UDF) which returns an array containing the names of all sheets:

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

This UDF is working fine, the formula {=SHEET_NAMES()} returns the expected array ({"DATA_1","DATA_2","DATA_3"}). (Note that the UDF needs to be considered as non-editable.)

My idea is now to use the UDF together with the functions ADDRESS() and INDIRECT() to reference cells A1 of all available worksheets dynamically, to use ISNUMBER() for checking whether a valid number is present, converting the boolean FALSE/TRUE to 0/1 by N() and finally, to use SUM() to sum up all the 0/1, like this:

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

However, the result is always 0, even if no worksheet contains an empty cell A1.

I found out that the ADDRESS() function returns the correct array of strings, representing the references to all the cells A1, which is {"DATA_1!$A$1","DATA_2!$A$1","DATA_3!$A$1"}.
INDIRECT() returns {#VALUE!,#VALUE!,#VALUE!} as it does not support arrays apparently, however the container function ISNUMBER() does support arrays, so this seems to do the iteration over the array elements properly, and it results to {TRUE,TRUE,FALSE}.
The N() function does the conversion in the intended way, hence it results in {1,1,0}.
But the final SUM() always results in 0, no matter how many sheets there are and whether or not some or all of them contain valid numbers in cell A1. (Just as a sidenote: if I enter the formula as a non-array function, the result depends on the first worksheet only.)

I tried to use NOT(ISBLANK()) and NOT(ISERROR()) instead of ISNUMBER(), and I tried to change SUM(N(ISNUMBER())) to SUM(COUNT()), but without success (all result in 0, except NOT(ISBLANK()) which gives 3).
(Also I even tried to replace ROW(A1) and COLUMN(A1) by ROWS($A$1:A1) and COLUMNS($A$1:A1), respectively, as this should overcome an array-related issue of INDIRECT(), according to some web pages, but with no change as well.)

So could you please tell me what I am doing wrong here, and how to overcome the failure without loosing flexibility?

(If possible, I would prefer a solution without using COUNTIF() or SUMIF() nor any other functions that require a certain condition to be given as a string, to maintain portability.)

Answer 1

You can try with this code:

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

Answer 2

You don't need all the sheet names, just the first and last:

=COUNT(Sheet1:Sheet3!A1)