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

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

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.

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

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