2

I have an Excel workbook with numerous worksheets containing data like:

    'DATA_1'                        'DATA_2'                        'DATA_3'        
    A       B                       A       B                       A       B       
1   value1     1.6              1   value1     0.8              1   value1     2.0  
2   value2     2.5              2   value2     2.2              2   value2     0.5  
3   value3     3.4              3   value3     3.1              3   value3     3.2  

There is another worksheet called SUMMARY, in front of all others, which looks like this:

    'SUMMARY'                       
    A       B       C       D       
5           min     max     count   
6   value1     1.0     1.8          
7   value2     1.5     2.5          
8   value3     2.0     3.0          

The rows 1 to 4 which are not shown are empty and hidden.

As you might already have guessed, I am now trying to fill the column count (D) properly. It should contain the count of all value? values throughout the DATA_? sheets that are in the respective range from min to max limits (a value equaling either of the limits is considered in range). So what I am expecting for the given sample data is:

    D       
5   count   
6        1  
7        2  
8        0  

What I have so far is a user-defined function to retrieve an array of worksheet names, which works fine:

Function SHEET_NAMES() As Variant
    ' returns names of all sheets as an array
    Dim index As Long, retArray() As String
    Application.Volatile True

    ReDim retArray(ThisWorkbook.Sheets.Count)
    For index = 1& To ThisWorkbook.Sheets.Count
        retArray(index) = ThisWorkbook.Sheets.Item(index).Name
    Next index
    SHEET_NAMES = retArray
End Function

Moreover, I can successfully access the value1 data (representatively) in all the worksheets with {=N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))}.

I am able to check the value1 values against their min limits with {=N(N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))>=B6)}. When this array function is spanned over 4 cells, it results in:

 0       1       0       1  

I am able to check the value1 values against their max limits with {=N(N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))<=C6)}. When this array function is spanned over 4 cells, it results in:

 1       1       1       0  

Finally, I am also able to test the cells for numeric values with {=N(ISNUMBER(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES()))))}. When this array function is spanned over 4 cells, it results in:

 0       1       1       1  

The 0 comes from the SUMMARY worksheet whose cell B1 is empty as already mentioned.

My idea is now to use function SUMPRODUCT finally and so to get the individual counts: {=SUMPRODUCT(N(N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))>=B6);N(N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))<=C6);N(ISNUMBER(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))))}.
However, although I expect 1 = (0 * 1 * 0) + (1 * 1 * 1) + (0 * 1 * 1) + (1 * 0 * 1), this always results in 0 strangely. So could you please tell me what am I doing wrong here?

(I would prefer a solution without using COUNTIF, COUNTIFS, SUMIF, SUMIFS, and other similar functions that need the condition to be given as a string, if possible.)

3
  • Are your value#'s always in the same position on every sheet? How many sheets is "numerous"?
    – Kyle
    Oct 20, 2015 at 13:15
  • Yes, the value? sheets all look exactly the same, the values are all at the same positions, only the values themselves are different; the number of sheets varies from 1 up to ~ 30...
    – aschipfl
    Oct 20, 2015 at 13:19
  • @ItWasn'tMe, sorry but I can't quite follow... are you referring to this question of mine?
    – aschipfl
    Jul 23, 2019 at 23:38

1 Answer 1

0

Change your UDF to look like this:

Function SHEET_NAMES() As Variant
    ' returns names of all sheets as an array
    Dim index As Long, retArray() As String, i As Long
    index = 1
    i = 1
    Application.Volatile True

    ReDim retArray(1 To ThisWorkbook.Sheets.Count - 1)
    Do While index <= ThisWorkbook.Sheets.Count
        If ThisWorkbook.Sheets(index).Name <> "SUMMARY" Then
            retArray(i) = ThisWorkbook.Worksheets(index).Name
            i = i + 1
        End If
        index = index + 1
    Loop
    SHEET_NAMES = retArray
End Function

The only really significant change is that I made it ignore the SUMMARY sheet.

And then use this array formula, adapted from here (confirm with ctrl+shift+enter):

=SUM(IF(N(INDIRECT(SHEET_NAMES()&"!B"&ROW()-5))>=B6,IF(N(INDIRECT(SHEET_NAMES()&"!B"&ROW()-5))<=C6,1,0),0))

Note that for this to work, your data on the SUMMARY sheet has to start on row 6 (like your sample). If it doesn't, you'll have to tweak the ROW()-5 bit so that it equals 1 on your first row. It has to work this way because that's what increments the row when you autofill the formula down.

Edit: Addressing your comments, use this formula. It can be used with your original UDF and utilizes ADDRESS() to create the references.

=SUM(IFERROR(IF(N(INDIRECT(ADDRESS(ROW(B1),COLUMN(B1),,,SHEET_NAMES())))>=B6,IF(N(INDIRECT(ADDRESS(ROW(B1),COLUMN(B1),,,SHEET_NAMES())))<=C6,1,0),0),0))
8
  • When I nest another IF with the ISNUMBER query, can I avoid modifying the SHEET_NAMES function? unfortunately, hard-coded exclusion of (a) certain sheet(s) is not a good option for me...
    – aschipfl
    Oct 20, 2015 at 18:02
  • ...moreover, is it necessary to remove the ADDRESS function? as far as I saw, that part worked well...
    – aschipfl
    Oct 20, 2015 at 18:10
  • @aschipfl see my edit.
    – Kyle
    Oct 20, 2015 at 18:44
  • Thanks, @Kyle, I'll test it soon... do you have an idea why my SUMPRODUCT approach is failing?
    – aschipfl
    Oct 20, 2015 at 23:27
  • @aschipfl It has something to do with your third argument, the N(ISNUMBER(... part, which I think you are using to filter out your SUMMARY sheet. When I used my UDF and took that part out it worked.
    – Kyle
    Oct 21, 2015 at 13:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .