Excel Formula to Auto-populate Start & End Values in Datasheet

[Mr Engineer]

Hi there

Context of problem: I am trying to quantify lengths of fencing along a road into excel. I type the extents of the fencing into Table 1.

Note that Start & End represents the location on the road. So for example, a fencing which starts at location "90" and runs to location "95" is 5 meters long.

I have a data table, which represents the area in 20 meter blocks.

Problem: I am trying to auto populate the total lengths of fencing into Column G. In the picture, I have manually entered these in, but I need a formula which can automate this.

Note I need to account for the overlap in the fencing between locations "80" to "100".

Can anybody give me a hand?

Thanks
Mr. Engineer

 

[StephenCrump]

Like this perhaps:

<table1[end])*(f3>G3 (array entered): {=SUMPRODUCT((E3< Table1[End])*(F3>Table1[Start]),IF(F3>Table1[End],Table1[End],F3)-IF(E3>Table1[Start],E3,Table1[Start]))}

Table1 is A2:C11 (with headers)


Excel 2010

	A	B	C	D	E	F	G
1	Table 1				Data Table
2	Start	End	Length		Start	End	Length
3	55	87	32		0	20	0
4	90	95	5		20	40	0
5	101	102	1		40	60	5
6	104	106	2		60	80	20
7	110	114	4		80	100	12
8	119	132	13		100	120	8
9	148	167	19		120	140	12
10	185	187	2		140	160	12
11	200	331	131		160	180	7
12					180	200	2
13			209		200	220	20
14					220	240	20
15					240	260	20
16					260	280	20
17					280	300	20
18					300	320	20
19					320	340	11
20					340	360	0
21					360	380	0
22
23							209

<tbody>
</tbody>

Sheet1​

</table1[end])*(f3>