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