Help with Box in box type calculation.

[wolf735]

Hello i am trying figure out a formula to where the number of boxes are counted as they are repeatedly put in new boxes.

this is the setup i am using.

Book1.xlsx
	G	H	I
2	parent	main	Qty
3		Tan Medium Box	1
4	Tan Medium Box	Red Small Box	2
5	Tan Medium Box	White Small Box	3
6	Red Small Box	Purple Compact Box	3
7	Red Small Box	Tan Compact Box	2
8	Red Small Box	Orange Compact Box	5
9	White Small Box	Yellow Compact Box	9
10	White Small Box	Gray Compact Box	2
11	White Small Box	White Compact Box	2
12	Yellow Compact Box	White Kit	3
13	Yellow Compact Box	Black Kit	6
14	Gray Compact Box	Maroon Kit	14
15	Yellow Compact Box	Gray Kit	7
16	Gray Compact Box	Tan Kit	1
17	White Compact Box	Green Kit	4
18	White Compact Box	Brown Kit	7
19	Purple Compact Box	White Kit	8
20	Tan Compact Box	Black Kit	5
21	Purple Compact Box	Maroon Kit	1
22	Tan Compact Box	Gray Kit	4
23	Orange Compact Box	Tan Kit	7
24	Orange Compact Box	Green Kit	3
25	Purple Compact Box	Brown Kit	8
Sheet5

Main -> parent then parent is recalculated as main.
I calculated each box type as a table, created 4 tables using sumifs formula the numbers past thou from top to bottom. Where i'm needing help is a formula that allows me to change in one table. the closest i get is the main going the parent of the same level. but not a main to parent on the level below.

Book1.xlsx
	G	H	I	J	K	L	M
2	parent	main	Qty
3		Tan Medium Box	1
4	Tan Medium Box	Red Small Box	2
5	Tan Medium Box	White Small Box	3
6	Red Small Box	Purple Compact Box	3
7	Red Small Box	Tan Compact Box	2
8	Red Small Box	Orange Compact Box	5
9	White Small Box	Yellow Compact Box	9
10	White Small Box	Gray Compact Box	2
11	White Small Box	White Compact Box	2
12	Yellow Compact Box	White Kit	3
13	Yellow Compact Box	Black Kit	6
14	Gray Compact Box	Maroon Kit	14
15	Yellow Compact Box	Gray Kit	7
16	Gray Compact Box	Tan Kit	1
17	White Compact Box	Green Kit	4
18	White Compact Box	Brown Kit	7
19	Purple Compact Box	White Kit	8
20	Tan Compact Box	Black Kit	5
21	Purple Compact Box	Maroon Kit	1
22	Tan Compact Box	Gray Kit	4
23	Orange Compact Box	Tan Kit	7
24	Orange Compact Box	Green Kit	3
25	Purple Compact Box	Brown Kit	8
Sheet5

This is the multiple table method i used.

Book1.xlsx
	R	S	T	U	V
2	parent	main	Qty	Totals	Hepler
3		Tan Medium Box	3
4
5
6	parent	main
7	Tan Medium Box	Red Small Box	2	6	3
8	Tan Medium Box	White Small Box	3	9	3
9
10
11	parent	main
12	Red Small Box	Purple Compact Box	1	6	6
13	Red Small Box	Tan Compact Box	3	18	6
14	Red Small Box	Orange Compact Box	5	30	6
15
16	White Small Box	Yellow Compact Box	1	9	9
17	White Small Box	Gray Compact Box	5	45	9
18	White Small Box	White Compact Box	8	72	9
19
20	parent	main
21	Tan Compact Box	Black Kit	1	18	18
22	Yellow Compact Box	Black Kit	1	9	9
23	Purple Compact Box	Brown Kit	1	6	6
24	White Compact Box	Brown Kit	1	72	72
25	Tan Compact Box	Gray Kit	1	18	18
26	Yellow Compact Box	Gray Kit	1	9	9
27	Orange Compact Box	Green Kit	1	30	30
28	White Compact Box	Green Kit	1	72	72
29	Gray Compact Box	Maroon Kit	1	45	45
30	Purple Compact Box	Maroon Kit	1	6	6
31	Gray Compact Box	Tan Kit	1	45	45
32	Orange Compact Box	Tan Kit	1	30	30
33	Purple Compact Box	White Kit	1	6	6
34	Yellow Compact Box	White Kit	1	9	9
35
Sheet5

Cell Formulas
Range	Formula
U7:U8,U21:U34,U16:U18,U12:U14	U7	=T7*V7
V7:V8	V7	=SUMIFS($T$3,$S$3,R7)
V12:V14,V16:V18	V12	=SUMIFS($U$7:$U$8,$S$7:$S$8,R12)
V21:V34	V21	=SUMIFS($U$12:$U$18,$S$12:$S$18,R21)

Thanks in advance.