Add DATE range in a SUMPRODUCT formula

[tvagge]

Hi,I have this formula:
=SUMPRODUCT(SUBTOTAL(3,OFFSET('ANALYSIS'!$E$1:$E$8865,ROW('ANALYSIS'!$E$1:$E$8865)-MIN(ROW('ANALYSIS'!$E$1:$E$8865)),,1))*('ANALYSIS'!$E$1:$E$8865=B2))
In my worksheet, I also have a column D, which contains dates.
Is it possible to add a date range (e.g Sep.01 - Sep.30) in the above formula ,so I can filter more my results ?

 

[jorismoerings]

try this: =SUMPRODUCT(SUBTOTAL(3,OFFSET('ANALYSIS'!$E$1:$E$8865,ROW('ANALYSIS'!$E$1:$E$8865)-MIN(ROW('ANALYSIS'!$E$1:$E$8865)),,1))*('ANALYSIS'!$E$1:$E$8865=B2)*('ANALYSIS'!$d$1:$d$8865>=date(2016,9,1)*('ANALYSIS'!$d$1:$d$8865<=date(2016,9,30))

 

[tvagge]

Well, this gives me a #N/A error...

 

[MARK858]

Are they real dates? Sep.01 looks like text if the dot is there.

What does =ISNUMBER(A1) give you where A1 is changed to one of the cells with a date in it?

 

[tvagge]

It gives me TRUE value. Yes, they are real dates.

 

[tvagge]

Hi guys,

I am still struggling on that, no solution found by me yet... Any other ideas ? I would be gratefull...

 

[steve the fish]

Looks to me theres an issue with the formula you were given with bracket placement. See if this works:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(ANALYSIS!$E$1:$E$8865,ROW(ANALYSIS!$E$1:$E$8865)-MIN(ROW(ANALYSIS!$E$1:$E$8865)),,1))*(ANALYSIS!$E$1:$E$8865=B2)*(ANALYSIS!$D$1:$D$8865>=DATE(2016,9,1))*(ANALYSIS!$D$1:$D$8865<=DATE(2016,9,30)))

 

[tvagge]

OK, it's working now! Thank you very much for the help!