From: Nathan356 on 19 May 2010 18:37 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: 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" wrote in message news:4cf2dbd1-363a-48a0-80d8-92eb1978a578(a)y6g2000pra.googlegroups.com...> 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! 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" wrote in message news:EAD30205-67E1-4CEA-841A-DD392C73AC2D(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" wrote in message > news:4cf2dbd1-363a-48a0-80d8-92eb1978a578(a)y6g2000pra.googlegroups.com... >> 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! > 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" wrote in message news:9039634C-77E7-409B-9E4E-3E5FAA22F338(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" wrote in message > news:EAD30205-67E1-4CEA-841A-DD392C73AC2D(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" wrote in message >> news:4cf2dbd1-363a-48a0-80d8-92eb1978a578(a)y6g2000pra.googlegroups.com... >>> 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! >> > From: Nathan356 on 20 May 2010 13:32 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 > > news:9039634C-77E7-409B-9E4E-3E5FAA22F338(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" wrote in message > >news:EAD30205-67E1-4CEA-841A-DD392C73AC2D(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" wrote in message > >>news:4cf2dbd1-363a-48a0-80d8-92eb1978a578(a)y6g2000pra.googlegroups.com.... > >>> 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  |  Next  |  Last Pages: 1 2 Prev: SumProduct using Date RangeNext: Summing multi-level bill of material