Try this version:
=SUMPRODUCT(SUBTOTAL(2,OFFSET(Data!I1,ROW(Data!I1:I1000)-ROW(Data!I1),0)),--(INT(Data!I1:I1000)>=B11),--(INT(Data!I1:I1000)<=B12))
I'm assuming that you want the count to be accurate when you apply a filter:
You should avoid using entire columns as range references with the SUMPRODUCT function (or any array formula) because it's very inefficient.when I filter column I for July 2010
The SUMPRODUCT function (and array formulas) will evaluate EVERY cell referenced. Based on the formula you just posted there are over 1 million empty cells in column I that would be evaluated which is a waste of resources and can cause overall file performance to slow down.
If data will be added frequently then use a dynamic defined range that will automatically adjust as data is added.
The dynamic range would be defined as...
Goto the Formulas tab>Formulas>Define name
Name: Dates
Refers to: =Data!$I$2:INDEX(Data!$I:$I,MATCH(1E100,Data!$I:$I))
Note that the dynamic range should not include a text column header. Only define the range that contains the actual data that needs to be calculated.
Then the formula would become:
=SUMPRODUCT(SUBTOTAL(2,OFFSET(INDEX(Dates,1),ROW(Dates)-MIN(ROW(Dates)),0)),--(INT(Dates)>=B11),--(INT(Dates)<=B12))
Bookmarks