# SUMPRODUCT with Date Range not working as expected

1. ## SUMPRODUCT with Date Range not working as expected

=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.  Register To Reply

2. ## Re: SUMPRODUCT with Date Range not working as expected

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

3. ## Re: SUMPRODUCT with Date Range not working as expected Originally Posted by Special-K 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.  Register To Reply

4. ## Re: SUMPRODUCT with Date Range not working as expected Originally Posted by Special-K 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.  Register To Reply

5. ## Re: SUMPRODUCT with Date Range not working as expected

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))  Register To Reply

6. ## Re: SUMPRODUCT with Date Range not working as expected

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
Formula:  `Please Login or Register  to view this content.`

If you are wanting a count of column k
Formula:  `Please Login or Register  to view this content.`  Register To Reply