Help with Excel Formulas

I would use a column of formulas to find the longest run for each trench - here is a working column of formulas:

=MAX(IF(B:B=B3,C:C))

There are array formulas that are entered using Ctrl-Shift-Enter

Named Ranges:

CostPerFoot: =Sheet1!$F$2:$F$41
Depth: =Sheet1!$E$2:$E$41
PricingLength: =Sheet1!$D$2:$D$41
TrenchHours: =Sheet1!$H$2:$H$41
TrenchLength: =Sheet1!$C$2:$C$41
TrenchNumber: =Sheet1!$B$2:$B$41
TrenchTotal: =Sheet1!$G$2:$G$41
WorkArea: =Sheet1!$A$2:$A$41

Replace cost/ft
=IF(E4=$AC$3,$AD$3,IF(E4=$AC$4,$AD$4,IF(E4=$AC$5,$AD$5,IF(E4=$AC$6,$AD$6,IF(E4=$AC$7,$AD$7,0)))))
with:

=VLOOKUP($E3,$AC$3:$AD$7,2,FALSE)

max lenght per trench using array formula in inserted helper column D:

=MAX(--(TrenchNumber=$B6)*TrenchLength)

I don't know how you will calculate trench total, either trench/hole length (col c) * col f, or, pricing length (helper col d) * col f?

Hello Kbass & Welcome to the Forum,

Administrative Note:

• We would love to continue to help you with your query, but first, before we can proceed…
• Please see Forum Rule #1 about proper thread titles and adjust accordingly...