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

Hi,
I am trying to solve this problem but not able to get the right results. Any help will be greatly appreciated.

I have a table with multiple columns and around 200 rows. The first column is Start Date and second column is End Date. What I need to find is the sum of the durations without counting the overlapping days twice. Example table is:

Start Date | End Date | Duration (including start day)
01/6/15 | 01/10/15 | 5
01/13/15 | 01/20/15 | 8
01/12/15 | 01/22/15 | 11
01/18/15 | 01/25/15 | 8

Sum of Duration is 32. But what I want is the sum counting overlapping days only once. This should be equal to 19 Days in this example.

Also I need to be able filter the table and the formula should only include visible rows in its calculation (ignore filtered rows while calculating sum of days).

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:

=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.

Hmm, try this.

I've added an additional helper column to determine whether a row is hidden or not in column C.

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

Formula in D2 (subsequently copied down)

Formula:

=SUBTOTAL(103,A2)

Formula in E2 (array formula)

Formula:

=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)),D2:D5)<>0))

See also attached.

Thanks Quekbc. This works. Really appreciate your help

Glad to help Lanksout. Definitely an interesting challenge for me.

If this resolves your query, can you please change the thread title to SOLVED by clicking on Thread Tools above your original post and click Mark this thread as solved.

Thanks Dave! I will check out this approach.

For now changing the thread to Solved.

Dave,

I couldn't get the formula to work. Its throwing in a pretty large value (number of days).

I have around 233 rows. I entered the array in row 3 (changed the values accordingly) and copied it down. Row 1 has the table header and the array was giving an error in Row2 (#Value). Also entered SUM function in Row 3 (next column) and copied it down. It didn't work. Hopefully I am doing it right.

Hi Quekbc,

I am encountering one issue. The "Not Hidden" column is causing some issues with excel filter.

Please see the attached excel file. I have named "Not Hidden" as Dummy. If I try filtering by Item - e.g. select only Item 1 the filter doesn't work properly. I narrowed the issue down to the "Dummy" column. But don't know how to correct it. The formula works perfectly as long as there is no filter applied.

It would be very helpful if you can suggest a solution.

Attached excel file
CountDaysWithoutOverlap2.xlsx

Thank You

Thanks Dave.

My apologies. I didn't update the references after copy pasting. I will cross check in detail and get back.

Dave, I think it's because the formula you are using does not take into consideration the entire date ranges as a whole. For example, if there are dates that overlaps, but are not in adjacent cells. For example, if you swap the last 2 dates at the bottom of the OPs example, the result would not be correct.

Row\Col	A	B	C	D
1	Start date	End date
2	06-Jan	10-Jan	0	=SUM(IF(FREQUENCY(ROW(INDIRECT($A2&":"&$B2)),IFERROR(ROW(INDIRECT($A1&":"&$B1)),0))=1,1,0))+$C1
3	13-Jan	20-Jan	0
4	18-Jan	25-Jan	3
5	12-Jan	22-Jan	7

The dummy column works properly. The error you're seeing is probably to do with the references in the formula are all incorrect. You are only referencing rows 2:5 rather than the entire table and you are referencing the buy price, rather than the dummy column.

Formula:

=SUM(1*(MMULT(((MIN(A2:A15)-ROW(A2)+ROW(OFFSET(A2,0,0,MAX(B2:B15)-MIN(A2:A15)+1,1)))>=TRANSPOSE(A2:A15))*((MIN(A2:A15)-ROW(A2)+ROW(OFFSET(A2,0,0,MAX(B2:B15)-MIN(A2:A15)+1,1)))<=TRANSPOSE(B2:B15)),F2:F15)<>0))

My apologies. I didn't update the references after copy pasting. Please see updated file. I have also applied a filter - for item 1. But it includes one item 2 value as well. I know the dummy is correct. It works for most of the elements. Its just that filter is throwing errors. So weird

CountDaysWithoutOverlap2.xlsx

Thanks

lanksout,

Yes, for some reason I am having the same issue as well. However, it's not an issue with the formula. For some reason, the filter doesn't treat Item 2 as not Item 1. I.e. the row is still visible (see row 15).

I was having the same issue with the file you sent earlier as well and I just assumed it was to do with my version of Excel. I think this warrants a separate thread as it seems to be a bug? For some reason I can't include row 15 as part of the filter - and I have no idea why.

Very curious indeed.