0

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

  • 1
    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
  • 1
    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
1

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
0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.