Total quantity of parts in an assembly calculation in a BOM

[cybertruck]

Hello Everyone, I am new to the forum. This will be my first ever thread so, please excuse me if there is anything wrong.
So my question is, is there a efficient way to calculate the total quantities of the parts in an assembly. The BOM I am working with comes with hierarchy level by lets say Main assembly - Item no. 1, sub assembly - Item.no 1.1, parts involved in that sub assembly are Item no. 1.1.1, 1.1.2 and so on. So what I do pretty much is multiply the quantity of sub assembly 1.1 to main assembly 1 and parts 1.1.1, 1.1.2 so on to the calculated total quantity of 1.1. So, I am just wondering if there is a way to make this process faster or easier so I dont have to calculate for everyone of them. Happy to hear any suggestions. I have attached a sample excel workbook for reference. Please let me know if you have any questions.

Thanks in advance.

 

[bobsan42]

That's all I could come up with:

Book1.xlsm
	A	B	C	D	E	F	G	H	I
1	level	Num	Qty	SubL	1	2	3	4	SUMS
2	1	1	1	TRUE	1	0	0	0	0
3	2	1.1	2	TRUE	1	2	0	0	0
4	3	1.1.1	2	FALSE	1	2	2		4
5	3	1.1.2	1	FALSE	1	2	1		2
6	3	1.1.3	2	TRUE	1	2	2	0	0
7	4	1.1.3.1	2	FALSE	1	2	2	2	8
8	4	1.1.3.2	1	FALSE	1	2	2	1	4
9	4	1.1.3.3	3	FALSE	1	2	2	3	12
10	4	1.1.3.4	1	FALSE	1	2	2	1	4
11	2	1.2	2	TRUE	1	2	0	0	0
12	3	1.2.1	2	FALSE	1	2	2		4
13	3	1.2.2	2	FALSE	1	2	2		4
14									42
Sheet8

Cell Formulas
Range	Formula
D2:D13	D2	=LEFT(B3,LEN(B2))=(B2)
E2:G13	E2	=IF($A2=E$1,$C2,IF($A2>E$1,E1,0))
H2:H13	H2	=IF($A2=H$1,$C2,IF($A2>H$1,H1,IF(D2,0,"")))
A2:A13	A2	=LEN(B2)-LEN(SUBSTITUTE(B2,".",""))+1
I2:I13	I2	=PRODUCT(E2:H2)
I14	I14	=SUM(I2:I13)

The Item No values must all be text to simplify life