Tracking overlap with a function

[MiniFav]

I am looking for an automatic way to identify overlap and still count totals acurately.
This might take a bit of explaining.

In the table below, Columns A,B and C show values 1 through to 50 group in data sets. The first row, I am looking at lines 1 through to 10 which is a total of 10 lines.

In Columns E,F,G and H show the same as column A,B and C except this time E3 (9) overlaps with Row F2 (10). So I have two data sets both looking at lines 9 and 10. I would like to see this overlap counted (Column H) and then subtracted from the total lines (G7).

Thing could get more complicated where in Columns J,K,L and M where the overlap happens in J9 meaning all row above up to and including row 2 now have overlapping lines.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Option 1	Option 2	Total		Option 1	Option 2	Total	Overlap		Option 1	Option 2	Total	Overlap
2	1	10	10		1	10	10	0		1	10	10	0
3	11	20	10		9	20	12	2		11	20	10	0
4	21	30	10		21	30	10	0		21	30	10	0
5	31	40	10		31	40	10	0		9	40	42	32
6	41	50	10		41	50	10	0		41	50	10	0
7		TOTAL	50			TOTAL	52	50			TOTAL	82	50
Sheet1

Cell Formulas
Range	Formula
C7,L7,G7	C7	=SUM(C2,C3,C5,C4,C6)

If the above makes sense and anyone have any suggestions for this using fomulas that would be fantastic.

 

[JvdV]

What about something along these lines:

=IF((A2:A6<A1:A5)*(A2:A6>1),B2:B6-A2:A6+1,)

 

[MiniFav]

What about something along these lines:

=IF((A2:A6<A1:A5)*(A2:A6>1),B2:B6-A2:A6+1,)

Thank you, amended slightly but that seems to be the solution!

Below is my table working if anyone in the future also needs it.

Book1.xlsx
	A	B	C	D
1	Option 1	Option 2	Total	Overlap
2	1	10	10	4
3	7	20	14
4	21	30	10	12
5	19	40	22
6	41	50	10
7		TOTAL	66	16
8		Total - overlap	50
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=IF((A3:A7<B2:B6)*(A3:A7>1),(B2:B6-A3:A7)+1,"")
D7	D7	=SUM(D2,D3,D4,D5,D6)
C2:C6	C2	=(B2-A2)+1
C7	C7	=SUM(C2,C3,C5,C4,C6)
C8	C8	=SUM(C2,C3,C5,C4,C6)-D7
Dynamic array formulas.