+ Reply to Thread
Results 1 to 2 of 2

Restrict SUMPRODUCT to a date range to see if a response target is met

  1. #1
    Registered User
    Join Date
    03-19-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    3

    Restrict SUMPRODUCT to a date range to see if a response target is met

    I've got the following data from a legacy system:

    Please Login or Register  to view this content.
    Using the following formula:

    Please Login or Register  to view this content.
    I can find how many were completed within x days, 7 for example. However this is applied to the whole data set. Let's say I wanted to know how many were completed in 7 days in January alone? Is there some way to filter the array to remove the dates I'm not interested before the above formula gets applied?

    I'm trying to keep this to a single cell formula, rather than creating hidden/dummy/helper columns to do the calculations for me for various stupid workplace reasons. Resolution to days is fine, hours/minutes is not needed.

    Maybe I'm going about this the wrong way?

    To aid understanding, what I'm trying to end up with is a stats worksheet that references this data (stored elsewhere and not editable) with something along the lines of the following:

    Please Login or Register  to view this content.
    Last edited by bappy; 01-09-2017 at 05:45 PM.

  2. #2
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Restrict SUMPRODUCT to a date range to see if a response target is met

    Try adding the MONTH() function like this:
    =SUMPRODUCT(--(NETWORKDAYS.INTL(A3:A12+0, B3:B12+0) <7)*(MONTH(A3:A12)=1))

    Note, I have changed the mult by one to "--" as this is more normal practice in the use of SUMPRODUCT.

    I hope this helps, please let me know!

    Regards,

    David


    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".


+ 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. Completion Percentage of a target date range
    By naseemta in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-30-2015, 12:20 AM
  2. Replies: 5
    Last Post: 12-16-2014, 05:47 PM
  3. [SOLVED] Change(ByVal Target As Range) does not work when Target value changes
    By LeonvL in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 01-07-2013, 06:59 PM
  4. [SOLVED] Worksheet_Change(ByVal Target As Range) - set to only update when 3 target cells changed?
    By trillium in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-07-2012, 06:40 PM
  5. Worksheet_CHange (ByVal Target as Range) when target is formula cell
    By coasterman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-09-2012, 07:00 PM
  6. Sumproduct formulas & slow response
    By Darby in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-21-2005, 05:25 PM
  7. Ranges:Target in Worksheet_SelectionChange(ByVal Target As Range)
    By Kevin McCartney in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-15-2005, 09:06 AM

Tags for this Thread

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