+ Reply to Thread
Results 1 to 6 of 6

SUMPRODUCT with Date Range not working as expected

  1. #1
    Registered User
    Join Date
    07-07-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    7

    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.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    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)))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    07-07-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    7

    Re: SUMPRODUCT with Date Range not working as expected

    Quote Originally Posted by Special-K View Post
    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.

  4. #4
    Registered User
    Join Date
    07-07-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    7

    Re: SUMPRODUCT with Date Range not working as expected

    Quote Originally Posted by Special-K View Post
    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.

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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))

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

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


    If you are wanting a count of column k
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by newdoverman; 01-13-2016 at 12:32 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Dynamic Named Range not working as expected
    By Leonthefixer in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-03-2015, 07:42 AM
  2. [SOLVED] FORMAT not working for Date to text as expected
    By JimSnyder in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-31-2013, 05:30 PM
  3. Excel VBA-Date format "mmm" not working as expected
    By sbishops in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-23-2009, 06:40 PM
  4. Excel VBA-Date format "mmm" not working as expected
    By sbishops in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2009, 06:39 PM
  5. Date format "mmm" not working as expected
    By sbishops in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-23-2009, 06:32 PM
  6. sumproduct w/horizontal range not working
    By dcd123 in forum Excel Formulas & Functions
    Replies: 66
    Last Post: 09-06-2005, 07:05 PM
  7. [SOLVED] Sumproduct w/date criteria not working
    By JANA in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-15-2005, 07:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1