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.)

`=SUM(--(N(INDIRECT(ADDRESS(ROW(A1),COLUMN(A1),,,SHEET_NAMES())))<>0))`

– Kyle Oct 27 '15 at 21:20`0`

can appear in the cells as well; the`--`

converts booleans to numbers (similar to`N()`

), right? – aschipfl Oct 28 '15 at 18:09`-`

converts`False`

to`0`

and`True`

to`-1`

, and the second`-`

converts`-1`

to`1`

. – Kyle Oct 28 '15 at 19:08