+ Reply to Thread
Results 1 to 8 of 8

SUMPRODUCT for Multiple Criteria

  1. #1
    Forum Contributor
    Join Date
    09-04-2013
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    259

    SUMPRODUCT for Multiple Criteria

    See attached workbook. Basically, it uses the SUMPRODUCT function to count the number of overlapping dates and times (cell E2 no down). However, I need to modify it to take into account another column - employee ID, as illustrated in Cell F2 on down (Overlap 2 - result only though, looking for revised formula).

    So basically, it would count the number of overlapping dates and times for each employee. Any ideas? Thanks.

    Time.xlsx

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    Re: SUMPRODUCT for Multiple Criteria

    This seems to work?
    =SUMPRODUCT(($B$2:$B$9=B2)*($A$2:$A$9+$C$2:$C$9>=A2+C2)*($A$2:$A$9+$C$2:$C$9<=A2+D2)+($A$2:$A$9+$C$2:$C$9<A2+C2)*($A$2:$A$9+$D$2:$D$9>=A2+C2))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    09-04-2013
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    259

    Re: SUMPRODUCT for Multiple Criteria

    Actually, it is giving me an incorrect count in the last two cells (G8 and G9)..Any idea why?

    As this example shows, there is only one employee/date that should have "2" displayed

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    Re: SUMPRODUCT for Multiple Criteria

    Sorry, try this...
    =SUMPRODUCT(--($B$2:$B$9=B2),($A$2:$A$9+$C$2:$C$9>=A2+C2)*($A$2:$A$9+$C$2:$C$9<=A2+D2)+($A$2:$A$9+$C$2:$C$9<A2+C2)*($A$2:$A$9+$D$2:$D$9>=A2+C2))

  5. #5
    Forum Contributor
    Join Date
    09-04-2013
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    259

    Re: SUMPRODUCT for Multiple Criteria

    For some reason, that one did not work either. But, I was able to figure it out based on your first example..I had to add the $B$2:$B$9=$B2 to the other side of the formula past the plus sign...Thanks for the quick response..

    =(SUMPRODUCT(($B$2:$B$9=$B2)*($A$2:$A$9+$C$2:$C$9>=A2+C2)*($A$2:$A$9+$C$2:$C$9<=A2+D2)+($B$2:$B$9=$B2)*($A$2:$A$9+$C$2:$C$9<A2+C2)*($A$2:$A$9+$D$2:$D$9>=A2+C2)))

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    Re: SUMPRODUCT for Multiple Criteria

    Curiois, it worked for me...
    A
    B
    C
    D
    E
    F
    1
    Date Employee ID Start Time End Time OverLap Overlap 2
    2
    01/05/2010
    123
    12:33
    14:15
    2
    2
    3
    01/05/2010
    345
    9:00
    10:00
    1
    1
    4
    01/05/2010
    123
    13:15
    17:30
    2
    2
    5
    02/14/2013
    123
    8:35
    10:15
    1
    1
    6
    02/14/2013
    345
    12:00
    14:00
    1
    1
    7
    06/01/2014
    123
    9:00
    13:15
    1
    1
    8
    06/01/2014
    345
    11:00
    16:15
    1
    1
    9
    06/01/2014
    456
    12:00
    13:00
    1
    1

    E2=SUMPRODUCT(--($B$2:$B$9=B2),($A$2:$A$9+$C$2:$C$9>=A2+C2)*($A$2:$A$9+$C$2:$C$9<=A2+D2)+($A$2:$A$9+$C$2:$C$9<A2+C2)*($A$2:$A$9+$D$2:$D$9>=A2+C2))
    copied across

    Note the use of --($B$2:$B$9=B2),(.... and not ($B$2:$B$9=B2)*(....

  7. #7
    Forum Contributor
    Join Date
    09-04-2013
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    259

    Re: SUMPRODUCT for Multiple Criteria

    Hmm...that time it did work. Must have copied it wrong the first time. Thank FDibbins..

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    Re: SUMPRODUCT for Multiple Criteria

    Happy to help, thanks for the feedback

+ 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] SUMPRODUCT of multiple columns with multiple criteria over multiple sheets
    By BellyGas in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-16-2015, 08:27 AM
  2. Replies: 5
    Last Post: 02-06-2015, 08:44 PM
  3. [SOLVED] Multiple criteria SUMPRODUCT (3 criteria) Excel 2003
    By lelrich in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-10-2014, 01:58 PM
  4. Replies: 1
    Last Post: 02-21-2014, 09:09 PM
  5. Replies: 6
    Last Post: 08-04-2013, 11:53 AM
  6. Replies: 5
    Last Post: 04-20-2012, 08:54 AM
  7. Sumproduct/sumif/indirect across multiple workbooks, worksheets, multiple criteria
    By robgardner15 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2011, 02:35 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