Summing multi-level bill of material

[Nathan356]

All,
I have a multi-level bill of material and I want to create a
function that will sum it properly without having to sum up each
level
manually. Here is an example:

Level Item Quantity Cost
1 Chair 1 $39
2 Seat 1 $15
3 Cushion 1 $10
3 Base 1 $5
2 Leg 4 $1
2 Back 1 $20
3 Leather 1 $12
3 Wood 1 $8

In my example, I have a chair. The chair is composed of a seat ($15),
four legs ($1 each), and a back ($20) for a total of $39. However, the
seat and back are composed of subcomponents. So, my input values
should look like this:

Level Item Quantity Cost
1 Chair 1
2 Seat 1
3 Cushion 1 $10
3 Base 1 $5
2 Leg 4 $1
2 Back 1
3 Leather 1 $12
3 Wood 1 $8

And I want excel to figure out the blanks for me using a formula. Can
this be done? Essentially I'm looking for a formula that will sum just
the level below it, until it runs into an equal level, and then it
stops. So, in the case of the seat, it should know to sum the cushion
and the base, but not the leather and the wood. Thanks for the help!

 

[Steve Dunn]

Hi Nathan,

This turned out to be more awkward than I expected, and there may be a
simpler solution. In the meantime try this.

In D2:

=SUMPRODUCT((OFFSET($A3,,,
MATCH(1,INDEX(($A3:$A$9=$A2)*
(ROW($A3:$A$9)-ROW($A3)),)))=$A2+1)*
OFFSET($D3,,,MATCH(1,INDEX(($A3:$A$9=$A2)*
(ROW($A3:$A$9)-ROW($A3)),)))*
OFFSET($C3,,,MATCH(1,INDEX(($A3:$A$9=$A2)*
(ROW($A3:$A$9)-ROW($A3)),))))

copy down into remaining blank cells.

HTH
Steve D.

 

[Steve Dunn]

Simplified:

=SUMPRODUCT(
(OFFSET($A3,,,MATCH(1,($A3:$A$9=$A2)+1))=$A2+1)*
OFFSET($D3,,,MATCH(1,($A3:$A$9=$A2)+1))*
OFFSET($C3,,,MATCH(1,($A3:$A$9=$A2)+1)))

 

[Steve Dunn]

Slight amendment:

=SUMPRODUCT(
(OFFSET($A3,,,MATCH(1,($A3:$A$9=$A2)+{1}))=$A2+1)*
OFFSET($D3,,,MATCH(1,($A3:$A$9=$A2)+{1}))*
OFFSET($C3,,,MATCH(1,($A3:$A$9=$A2)+{1})))

 

[Nathan356]

Slight amendment:

=SUMPRODUCT(
(OFFSET($A3,,,MATCH(1,($A3:$A$9=$A2)+{1}))=$A2+1)*
OFFSET($D3,,,MATCH(1,($A3:$A$9=$A2)+{1}))*
OFFSET($C3,,,MATCH(1,($A3:$A$9=$A2)+{1})))






- Show quoted text -

Steve,
Thanks! That worked perfectly. -Nathan

 

[Steve Dunn]

You're welcome, thanks for the feedback.

 

[ziveli]

Hi,

I have a similar problem, but a bit simpler. I need function that will sum by the level, but without the multiplying with quantity. Same example as Nathan's, but without quantity column.

Thanks