If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Summing multi-level bill of material
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! |
#2
|
|||
|
|||
Summing multi-level bill of material
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. "Nathan356" wrote in message ... 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! |
#3
|
|||
|
|||
Summing multi-level bill of material
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" wrote in message ... 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. "Nathan356" wrote in message ... 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! |
#4
|
|||
|
|||
Summing multi-level bill of material
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}))) "Steve Dunn" wrote in message ... 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" wrote in message ... 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. "Nathan356" wrote in message ... 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! |
#5
|
|||
|
|||
Summing multi-level bill of material
On May 20, 4:32*am, "Steve Dunn" wrote:
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}))) "Steve Dunn" wrote in message ... 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" wrote in message ... 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. "Nathan356" wrote in message .... 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!- Hide quoted text - - Show quoted text - Steve, Thanks! That worked perfectly. -Nathan |
#6
|
|||
|
|||
Summing multi-level bill of material
You're welcome, thanks for the feedback.
|
Thread Tools | |
Display Modes | |
|
|