+ Reply to Thread
Results 1 to 19 of 19

How to find Sum of # of days w/o counting overlapping days twice

Hybrid View

  1. #1
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: How to find Sum of # of days w/o counting overlapping days twice

    Fun challenge, use this formula.

    Row\Column
    A
    B
    C
    D
    1 Start date End date Duration Duration (no overlap)
    2 6-Jan 10-Jan 5 19
    3 13-Jan 20-Jan 8
    4 12-Jan 22-Jan 11
    5 18-Jan 25-Jan 8

    Formula in D1 (array formula)
    Formula: copy to clipboard
    =SUM(1*(MMULT(((MIN(A2:A5)-ROW(A2)+ROW(OFFSET(A2,0,0,MAX(B2:B5)-MIN(A2:A5)+1,1)))>=TRANSPOSE(A2:A5))*((MIN(A2:A5)-ROW(A2)+ROW(OFFSET(A2,0,0,MAX(B2:B5)-MIN(A2:A5)+1,1)))<=TRANSPOSE(B2:B5)),1*(ROW(A2:A5)>0))<>0))

    This is an array formula, please copy in the formula, then hit CTRL SHIFT ENTER to finalise the formula rather than just enter.

  2. #2
    Registered User
    Join Date
    07-19-2015
    Location
    NONE
    MS-Off Ver
    Excel
    Posts
    9

    Re: How to find Sum of # of days w/o counting overlapping days twice

    Thank You for your reply. I inserted the function and it works.
    In addition I also need it to work when one or more rows are filtered out (by applying filter in one of the columns). Currently this formula includes both visible and hidden rows (from filter). I want it to work only with visible rows.

    If you could help with that would be awesome though I can't ask more.

    Thanks again.
    Last edited by lanksout; 08-09-2015 at 09:59 PM.

+ 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] Calculating # of Overlapping days with several date ranges and conditions
    By ZafferAhmed in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-30-2021, 05:44 AM
  2. [SOLVED] Find days between days, but including the starting date.
    By Jocamo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2015, 10:37 PM
  3. Calculating # of Overlapping days with several date ranges.
    By CarlSVM in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-06-2014, 07:09 AM
  4. Replies: 0
    Last Post: 07-30-2012, 04:51 PM
  5. Counting Continuous Days within overlapping Date Ranges
    By mgaworecki in forum Excel General
    Replies: 3
    Last Post: 09-07-2011, 08:33 AM
  6. Replies: 9
    Last Post: 09-03-2007, 08:26 PM
  7. Dividing a time span into shifts - overlapping days
    By Heidi in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 02-27-2006, 10:14 PM

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