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

**lanksout** · 08-09-2015, 05:36 PM

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

**quekbc** · 08-09-2015, 07:49 PM

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:

Please Login or Register  to view this content.

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

**lanksout** · 08-09-2015, 09:32 PM

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.

**quekbc** · 08-09-2015, 10:03 PM

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:

Please Login or Register  to view this content.

Formula in E2 (array formula)

Formula:

Please Login or Register  to view this content.

See also attached.

**lanksout** · 08-09-2015, 10:57 PM

Thanks Quekbc. This works. Really appreciate your help

**quekbc** · 08-09-2015, 11:19 PM

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.

**FlameRetired** · 08-09-2015, 11:36 PM

Yes this is interesting and challenging. Arrived at a different approach.

Row\Col	A	B	C	D	E
1	Start	End
2	**1/6/2015	1/10/2015	0	0	Array-entered C2 : {=SUM(IF(FREQUENCY(ROW(INDIRECT($A2&":"&$B2)),IFERROR(ROW(INDIRECT($A1&":"&$B1)),0))=1,1,0))+$C1}
3	**1/13/2015	1/20/2015	0	0	D2 : =SUM($C$2:$C2)
4	**1/12/2015	1/22/2015	7	7
5	**1/18/2015	1/25/2015	12	19

**lanksout** · 08-10-2015, 11:06 AM

Thanks Dave! I will check out this approach.

For now changing the thread to Solved.

**lanksout** · 08-10-2015, 12:21 PM

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.

**FlameRetired** · 08-10-2015, 01:14 PM

I can not say without seeing a small workbook example, but the first row of the formula references row 1 which is understood to have column headers. That is why

"IFERROR(ROW(INDIRECT($A1&":"&$B1)),0)"

is the second argument of the IF function. A #REF! error is anticipated and assigned a 0 value. I don't know where the #Value error is coming from.

Can you upload an actual workbook example --- a small one, but large enough to demonstrate what you are describing?

Oh yes. Are you certain that the dates you are referencing are actual dates and not text that looks like dates? Been there done that.

**quekbc** · 08-10-2015, 05:59 PM

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

**FlameRetired** · 08-10-2015, 07:01 PM

@ quekbc

Thank you. That's right. I missed that one.

Hope OP sees it.

**lanksout** · 08-11-2015, 12:19 AM

Hi Dave,

Please find the attached file. I want to calculate the total duration (counting overlaps only once) without filter and also after filtering by Items column. E.g. select only item 1 and find its duration (counting overlap only once). Hopefully this gives a better picture.

CountDaysWithoutOverlap1.xlsx

Thanks

**lanksout** · 08-11-2015, 12:25 AM

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

**quekbc** · 08-11-2015, 12:55 AM

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:

Please Login or Register  to view this content.

**FlameRetired** · 08-11-2015, 01:03 AM

Late Edit: These results (column G) can't be right. There are only 112 days in the entire range.

I have not checked these results extensively. (I have now.) They are below. Bearing in mind what quekbc observed please check this out. The errors occurred because wrong cell/range references were in the latest upload. What was there pertained to the previous upload. That part has been corrected here, but check the results. Quekbc has a good point and I haven't found a work-around for that yet.

BTW this is without filtering. I am not sure I understand how you want this to behave when filtered.

Row\Col	A	B	C	D	E	F	G	H
1	Buy Date	Sell Date	Item	Buy Price	Sell Price	Array	Duration	Column1
2	11/3/2011	11/12/2011	Item 1	120.19	115.16	0	0	Array enter in F2 :{=SUM(IF(FREQUENCY(ROW(INDIRECT($A2&":"&$B2)),IFERROR(ROW(INDIRECT($A1&":"&$B1)),0))=1,1,0))+N($F1)}
3	11/28/2011	12/8/2011	Item 2	78	76.52	0	0	In G2 :=SUM($F$2:$F2)
4	12/16/2011	12/28/2011	Item 3	53.1	53.98	0	0
5	12/20/2011	12/28/2011	Item 2	16.87	17.19	9	9
6	12/21/2011	12/28/2011	Item 2	47.49	46.97	17	26
7	12/22/2011	12/28/2011	Item 1	80.97	81.15	24	50
8	12/22/2011	12/28/2011	Item 1	49.66	49.12	31	81
9	12/20/2011	1/11/2012	Item 2	82	85.08	37	118
10	12/21/2011	1/12/2012	Item 3	68.4	69.79	60	178
11	12/20/2011	1/26/2012	Item 3	20.66	22.16	82	260
12	1/17/2012	1/27/2012	Item 1	124.62	126.45	93	353
13	1/18/2012	1/30/2012	Item 2	71.08	71.14	103	456
14	2/13/2012	2/15/2012	Item 1	37.48	36.8	103	559
15	12/22/2011	2/22/2012	Item 2	125.27	136.03	105	664

**lanksout** · 08-11-2015, 01:10 AM

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** · 08-11-2015, 01:12 AM

Thanks Dave.

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

**quekbc** · 08-11-2015, 02:15 AM

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.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Calculating # of Overlapping days with several date ranges and conditions

[SOLVED] Find days between days, but including the starting date.

Calculating # of Overlapping days with several date ranges.

Counting and summing the total number of overlapping days in two periods

Counting Continuous Days within overlapping Date Ranges

Counting the days worked and days remaining on a schedule automatically

Dividing a time span into shifts - overlapping days

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions