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

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

• I believe this works if there are no zeros: `=SUM(--(N(INDIRECT(ADDRESS(ROW(A1),COLUMN(A1),,,SHEET_NAMES())))<>0))` – Kyle Oct 27 '15 at 21:20
• Yes, it works -- thanks, @Kyle; this is not a perfect solution though as `0` can appear in the cells as well; the `--` converts booleans to numbers (similar to `N()`), right? – aschipfl Oct 28 '15 at 18:09
• That's right. The first `-` converts `False` to `0` and `True` to `-1`, and the second `-` converts `-1` to `1`. – Kyle Oct 28 '15 at 19:08

You can try with this code:

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

• Thanks for replying, but I'm afraid this cannot work as the additional `N()` forces the result of `INDIRECT()` to be converted to a numeric value, so the outer `ISNUMBER()` will return `TRUE` for every cell, so applied on the example from the question, this results in `3`, although I expect `2` (since one of the three cells of interest is blank)... – aschipfl Oct 27 '15 at 20:12
• Apologies. Good point. Your preference for not allowing COUNTIF makes things a little complex! Can I ask if zeroes are a possibility in the cells being referenced? – XOR LX Oct 27 '15 at 21:10
• Yes, zeros are considered valid numbers. – aschipfl Oct 27 '15 at 21:19
• Ok, have posted a second attempt. SUMPRODUCT negates the need for CSE, though if you really insist then of course you can replace that function with SUM and commit appropriately. – XOR LX Oct 27 '15 at 21:25
• @XORLX, thanks, it works as expected! could you please 1. add your comment info concerning CSE to the answer, and 2. provide some explanations what happens here and why this is working, and why my original formula did not? – aschipfl Oct 28 '15 at 9:24

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

``````=COUNT(Sheet1:Sheet3!A1)
``````
• This does not answer the question, the number and names of the sheets are dynamic. – Kyle Oct 27 '15 at 21:15