Calculating total quantity through multilevel BOM structure

hi all,

At my new job i do a lot of costcalculations through Bill of materials of customers. Therefore i need to understand the total usage of a row/partnumber througout all the levels.

Column A (BOM LEVEL) Column B (Quantity) Column C (QTY multilevel)
1 1 (should be = 1)
1.1 2 (should be = 1 x 2 = 2)
1.1.1 3 (should be = 1 x 2 x 3 = 6)
1.1.2 2 (should be = 1 x 2 x 2 = 4)
... up to 6 levels deep


Normally i then create new columns to find the parent level, VLOOKUP and then multiply etc. But is there a formula that i can copy in column C to do all this work in once and calculate through the total usage by finding the parent, multiply etc?

Thanks for you help already.

Originally Posted by Everstrivin

I'm sorry, but this isn't very clear.

Was your third row supposed to say 1.1.1 3 (should be = 1*2*3=6), or was it supposed to be 1.1.2 3?

Could you help me figure out the math?
Is it: if there is a 1, then 1 x if there is a 2, then 2 x the Quantity?

Sorry to made it not clear. Column a is the levelposition (no calculation with this). Column a is only showing the levelpath. So the deepest level should multiply against all previous/parent level quantities: example: 1 machine (level 1), in this machine there is a identical section 2 times in(level 1.1) and in one section are 3 bolts. To determine the total usage of that bolt its level 1 (1 pcs) x level 1.1 (2pcs) x level 1.1.1 (3pcs)= 6pcs of that bolt in that machines.

And this calculation i would like to have in one column C