#### Bearcat Brew

##### New Member
I have a spreadsheet that has a series of single level bill of materials. Column A is the part number, Column B is the bill of material level, Column C is the parent for the part number, column D is the lead time/processing time for the part (total time to order the part and assemble it into the parent). I am trying to calculate the total lead time for each part using a formula in column E. For an example, a level 3 part would add together its lead time, the lead time for its parent (level 2 part), and the lead time of the parent's parent (level 1 part). Note: we are not considering level 0 since assembling all of the subassemblies to it takes relatively no time.
If the parts were listed in order from parent to lowest level child or child to parent, I have seen solutions here for calculating BOM quantities that could be potentially adapted to sum lead times, but that is not the case here - the parts can be listed in any order. My current solution is not elegant whatsoever and only goes down to a level 5 part. It is a series of progessively more nested VLOOKUP terms that are summed:

=D2+IF(B2>1,VLOOKUP(C2,A\$2:D\$41,4,FALSE),0)+IF(B2>2,VLOOKUP(VLOOKUP(C2,A\$2:C\$41,3,FALSE),A\$2:D\$41,4,FALSE),0)+IF(B2>3,VLOOKUP(VLOOKUP(VLOOKUP(C2,A\$2:C\$41,3,FALSE),A\$2:C\$41,3,FALSE),A\$2:D\$41,4,FALSE),0)+IF(B2>4,VLOOKUP(VLOOKUP(VLOOKUP(VLOOKUP(C2,A\$2:C\$41,3,FALSE),A\$2:C\$41,3,FALSE),A\$2:C\$41,3,FALSE),A\$2:D\$41,4,FALSE),0)+IF(B2>5,VLOOKUP(VLOOKUP(VLOOKUP(VLOOKUP(VLOOKUP(C2,A\$2:C\$41,3,FALSE),A\$2:C\$41,3,FALSE),A\$2:C\$41,3,FALSE),A\$2:C\$41,3,FALSE),A\$2:D\$41,4,FALSE),0)

If I end up having a level 6 part, I will need to add an even longer term onto the end of this formula and if I end up with a level 7 part at some future point, I think the formula will run too long to fit in a cell. Is there a better way of doing this? I have been racking my brain thinking that SUMPRODUCT can somehow come to the rescue, but have come up empty so far.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### wisewood

##### Board Regular
Without me having to decode those nested VLOOKUP's - is there a specific location that it's looking for the lead times of these various levels?

Ie. Sheet2 A1:A7 ??

Should be relatively easy to create a VBA script to do this quite efficiently I would have thought.

#### Bearcat Brew

##### New Member
Column D has the lead times. The solution needs to be a formula, no VBA this time.

The formula listed in the original post is in Cell E2 It starts with D2 (its own lead time), then sums terms that find the parent, parent's parent, etc., as many levels as needed until it gets to the highest level part.
Book2
ABCDE
215981516481101203
3164782179021428
4164793164781442
51648141647960102
6169113176969092
716912217902721
816913217902721
916931217902721
1016942217902721
11171441181119292
121741831769190105
131754311811111
141754521754312
15176212176223031
161762211811111
17176912176951415
181769511811111
191769621769512
201769721769512
211783821769578
22178653175453032
23178931181116060
24178961181113030
25178971181113030
26178991181111414
27179021181111414
Sheet1

Replies
7
Views
478
Replies
4
Views
270
Replies
6
Views
244
Replies
4
Views
73
Replies
1
Views
55

1,191,689
Messages
5,988,032
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back