# Bill of materials quantity

#### nigelbloomy

##### New Member
I'm using Excel 2007 on Windows XP. I have many different Bill of materials that come to me in the format below. I would like a formula in column D that can tell me the total quantity that will be used to make the top level part. For example: For part e, I will use 1 * .6(at level 2) *1 (at level 1). So I will use a total of .6 of part e to make my top level part a.

I tried using sum product to find anything lower than each level. My problem with that is when I multiply the quantity of part e by all part levels lower than 3 I get the .6 from the correct level 2, but I also get the .5 from the the other level 2 (part b).

I have been trying formulas to do this, but I think it may need VBA code to get it right.

 Level Part Quantity 1 a 1 2 b .5 3 c .01 2 d .6 3 e 1 4 f .2 5 g 3 4 h 6

<tbody>
</tbody>

#### shg

##### MrExcel MVP
Welcome to the board.

Code:
``````      --A-- -B-- ---C---- --D---
1   Level Part Quantity  Ext
2      1  a          1      1
3      2  b        0.5    0.5
4      3  c       0.01  0.005
5      2  d        0.6    0.6
6      3  e          1    0.6
7      4  f        0.2   0.12
8      5  g          3   0.36
9      4  h          6    3.6``````
The formula in D2 and copied down is

=C2*IF(A2=1, 1, LOOKUP(2, 1/(A\$1:A2=A2-1), D\$1:D1))

#### Marcelo Branco

##### MrExcel MVP
Welcome to the board.

Code:
``````      --A-- -B-- ---C---- --D---
1   Level Part Quantity  Ext
2      1  a          1      1
3      2  b        0.5    0.5
4      3  c       0.01  0.005
5      2  d        0.6    0.6
6      3  e          1    0.6
7      4  f        0.2   0.12
8      5  g          3   0.36
9      4  h          6    3.6``````
The formula in D2 and copied down is

=C2*IF(A2=1, 1, LOOKUP(2, 1/(A\$1:A2=A2-1), D\$1:D1))
Nice!!!

M.

#### shg

##### MrExcel MVP
Thank you, Marcelo. Or slightly differently,

=C2*IF(A2=1, 1, LOOKUP(9E307, D\$1:D1/(A\$1:A2=A2-1)))

Last edited:

#### nigelbloomy

##### New Member
shg you are incredible. I have spent weeks trying to figure this out using offset, match and sumproduct functions. I don't know how to thank you enough for figuring this out so quickly.

#### rt2017

##### New Member
Hello, I'm using Excel 2010 and I have a similar problem for a more complex bill of materials.

My BOM use top level assemblies, sub-assemblies and sub-sub-assemblies, and sub-components.

For example:

Item Code Quantity

1 A 2

1.1 B 2

1.2 B2 4

1.2.1 B21 2

1.3 B3 2

2 C1 1

2.1 C4 5

I need to create a fourth column with the total quantity for the main assembly. So, for example, we will need 16 items from 1.2.1 (Multiplying 2 by 4 and by 2).

Is there a similar equation that we can use to calculate automaticaly?

Thank you very much!

1,081,530
Messages
5,359,353
Members
400,524
Latest member
Excelbat

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...