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

#### Mr Engineer

##### New Member
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

##### Well-known Member
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]))}

Excel 2010
ABCDEFG
1Table 1Data Table
2StartEndLengthStartEndLength
35587320200
49095520400
5101102140605
61041062608020
711011448010012
8119132131001208
91481671912014012
10185187214016012
112003311311601807
121802002
1320920022020
1422024020
1524026020
1626028020
1728030020
1830032020
1932034011
203403600
213603800
22
23209

<tbody>
</tbody>
Sheet1

</table1[end])*(f3>