# How to count number of cells over multiple worksheets whose values are in between a given range?

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

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