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

  • Are your value#'s always in the same position on every sheet? How many sheets is "numerous"? – Kyle Oct 20 '15 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 '15 at 13:19
  • @ItWasn'tMe, sorry but I can't quite follow... are you referring to this question of mine? – aschipfl Jul 23 '19 at 23:38
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))
  • 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 '15 at 18:02
  • ...moreover, is it necessary to remove the ADDRESS function? as far as I saw, that part worked well... – aschipfl Oct 20 '15 at 18:10
  • @aschipfl see my edit. – Kyle Oct 20 '15 at 18:44
  • Thanks, @Kyle, I'll test it soon... do you have an idea why my SUMPRODUCT approach is failing? – aschipfl Oct 20 '15 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 '15 at 13:12

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.