Prev: SumProduct using Date Range
Next: Summing multi-level bill of material
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" <nathankwok(a)gmail.com> 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" <stunn(a)sky.com> 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" <nathankwok(a)gmail.com> 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" <stunn(a)sky.com> 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" <stunn(a)sky.com> 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" <nathankwok(a)gmail.com> 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" <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: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" <st...(a)sky.com> 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" <nathank...(a)gmail.com> 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 Range
Next: Summing multi-level bill of material