SUMPRODUCT with Date Range not working as expected

[redsheep79]

=SUMPRODUCT((Data!$K$2:$K$500>=E3)*(Data!$K$2:$K$500<=E4))

Formula above shows range of cells with dates in (K2-K500 in tab called Data) and then E3 and E4 are start and end dates.

E3 = 01/11/2015
E4 = 30/11/2015


However the total returned is not including those cells that have 30/11/15. The cells that contain the dates also have time stamps on them (eg 30/11/2015 10:48:08) so I assume the time stamp is doing something to scewiff the data. Can I do anything in the formula or in the cells that contain the start/end dates to incorporate time stamps or should i just amend the end date to 01/12/15?

Thank you.

[Special-K]

Try
=SUMPRODUCT((Data!$K$2:$K$500>=INT(E3))*(Data!$K$2:$K$500<=INT(E4)))

[redsheep79]

Try
=SUMPRODUCT((Data!$K$2:$K$500>=INT(E3))*(Data!$K$2:$K$500<=INT(E4)))

Thank you for the reply but that gives the same result.

[redsheep79]

Try
=SUMPRODUCT((Data!$K$2:$K$500>=INT(E3))*(Data!$K$2:$K$500<=INT(E4)))

Thank you for the reply but that gives the same result.

[Jonmo1]

It's the timestamps in column K.

Assuming the dates in E3 and E4 are just dates, no time
When time is omitted from a Date, then it becomes Midnight (0:00:00)

If say K 10 is
30/11/2015 9:00:00
Then that is in fact NOT Less Than or Equal to 30/11/2015 0:00:00

Try
=SUMPRODUCT((Data!$K$2:$K$500>=E3)*(Data!$K$2:$K$500<E4+1))

[newdoverman]

If the dates corresponding for the data are in column L and the data is in column L on the Data worksheet this will calculate the values in Data!L2:L500

Please Login or Register  to view this content.

If you are wanting a count of column k

Please Login or Register  to view this content.