From: Nathan356 on 19 May 2010 18:37 All, I have a multilevel 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: Item
From: Steve Dunn on 20 May 2010 04:48 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" <nathankwok(a)gmail.com> wrote in message news:4cf2dbd1363a48a080d892eb1978a578(a)y6g2000pra.googlegroups.com... > All, > I have a multilevel 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!
From: Steve Dunn on 20 May 2010 05:53 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" <stunn(a)sky.com> wrote in message news:EAD3020567E14CEA841ADD392C73AC2D(a)microsoft.com... > 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" <nathankwok(a)gmail.com> wrote in message > news:4cf2dbd1363a48a080d892eb1978a578(a)y6g2000pra.googlegroups.com... >> All, >> I have a multilevel 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! >
From: Steve Dunn on 20 May 2010 07:32 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" <stunn(a)sky.com> wrote in message news:9039634C77E7409B9E4E3E5FAA22F338(a)microsoft.com... > 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" <stunn(a)sky.com> wrote in message > news:EAD3020567E14CEA841ADD392C73AC2D(a)microsoft.com... >> 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" <nathankwok(a)gmail.com> wrote in message >> news:4cf2dbd1363a48a080d892eb1978a578(a)y6g2000pra.googlegroups.com... >>> All, >>> I have a multilevel 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! >> >
From: Nathan356 on 20 May 2010 13:32 On May 20, 4:32 am, "Steve Dunn" <st...(a)sky.com> 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" <st...(a)sky.com> wrote in message > > news:9039634C77E7409B9E4E3E5FAA22F338(a)microsoft.com... > > > > > 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" <st...(a)sky.com> wrote in message > >news:EAD3020567E14CEA841ADD392C73AC2D(a)microsoft.com... > >> 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" <nathank...(a)gmail.com> wrote in message > >>news:4cf2dbd1363a48a080d892eb1978a578(a)y6g2000pra.googlegroups.com.... > >>> All, > >>> I have a multilevel 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

Next

Last
Pages: 1 2 Prev: SumProduct using Date Range Next: Summing multilevel bill of material 