Automate formulas in a list using code

[Son]

Hello, I have a spreadsheet with employees according to their position in the company.
The data contains the code for each hiearchical position, the name of the position, and the number of employees in each hierarchical position.
The grey rows sum the number of employees for each section or department.
The problem is that the formulas in column C (counting the employees according to hierarchy level), are not automatic, I need to input specific ranges for each, by hand.
But, if I change the hierarchal structure, I need to rewrite the formulas by hand.

Is there a way that I can make the formulas automated, eg by using code? Or perhaps I could use summing by colour?
I cannot use a pivot because I need to use the data as a list, as I need to apply various filters.
I have already posted this in another thread, but I repost using xl2bb since I believe this would be more helpful.

Any ideas would be most appreciated!!! I'm looking forward to hearing your thouhts!

test.xls
	A	B	C
1
2
3	1	= 1 means I have filters applied
4
5
6	POSITION CODE	POSITION IN HIERARCHY	COUNT STAFF
7	1	SECTION 1	2
8	1.0	MANAGER'S 1 OFFICE	2
9	1.0.1	MANAGER 1	1
10	1.0.2	ASSISTANT MANAGER	1
11	2	SECTION 2	295
12	2.0	MANAGER'S 1 OFFICE	3
13	2.0.1	MANAGER 1	1
14	2.0.2	ASSISTANT MANAGER 1	2
15	2.0.3	ASSISTANT MANAGER 2	0
16	2.1	MANAGER'S 2 OFFICE	5
17	2.1.1	MANAGER 2	5
18	2.2	MANAGER'S 3 OFFICE	40
19	2.2.1	MANAGER 3	5
20	2.2.2	ASSISTANT 1	10
21	2.2.3	ASSISTANT 2	5
22	2.2.4	ASSISTANT 3	10
23	2.2.5	ASSISTANT 4	10
24	2.3	MANAGER'S 4 OFFICE	45
25	2.3.1	MANAGER 4	5
26	2.3.2	ASSISTANT 1	10
27	2.3.3	ASSISTANT 2	10
28	2.3.4	ASSISTANT 3	10
29	2.3.5	ASSISTANT 4	10
30	2.4	MANAGER'S 5 OFFICE	25
31	2.4.1	MANAGER 5	5
32	2.4.2	ASSISTANT 1	5
33	2.4.3	ASSISTANT 2	5
34	2.4.4	ASSISTANT 3	5
35	2.4.5	ASSISTANT 4	5
36	2.5	MANAGER'S 6 OFFICE	78
37	2.5.1	DAY PERSONNEL	14
38	2.5.1.1	MANAGER 6	1
39	2.5.1.2	ASSISTANT 1	3
40	2.5.1.3	ASSISTANT 2	6
41	2.5.1.4	ASSISTANT 3	2
42	2.5.1.5	ASSISTANT 4	0
43	2.5.1.6	ASSISTANT 5	2
44	2.5.2	SHIFT PERSONNEL	64
45	2.5.2.1	SHIFT MANAGER	5
46	2.5.2.2	ASSISTANT 1	5
47	2.5.2.3	ASSISTANT 2	5
48	2.5.2.4	ASSISTANT 3	5
49	2.5.2.5	ASSISTANT 4	5
50	2.5.2.6	ASSISTANT 5	15
51	2.5.2.7	ASSISTANT 6	5
52	2.5.2.8	ASSISTANT 7	5
53	2.5.2.9	ASSISTANT 8	5
54	2.5.2.10	ASSISTANT 9	5
55	2.5.2.11	ASSISTANT 10	0
56	2.5.2.12	ASSISTANT 11	0
57	2.5.2.13	ASSISTANT 12	0
58	2.5.2.14	ASSISTANT 13	4
59	2.6	MANAGER'S 7 OFFICE	48
60	2.6.1	DAY PERSONNEL	13
61	2.6.1.1	MANAGER 7	1
62	2.6.1.2	ASSISTANT 1	3
63	2.6.1.3	ASSISTANT 2	9
64	2.6.1.4	ASSISTANT 3	0
65	2.6.1.5	ASSISTANT 4	0
66	2.6.2	SHIFT PERSONNEL	35
67	2.6.2.1	SHIFT MANAGER	5
68	2.6.2.2	ASSISTANT 1	5
69	2.6.2.3	ASSISTANT 2	5
70	2.6.2.4	ASSISTANT 3	5
71	2.6.2.5	ASSISTANT 4	5
72	2.6.2.6	ASSISTANT 5	5
73	2.6.2.7	ASSISTANT 6	0
74	2.6.2.8	ASSISTANT 7	0
75	2.6.2.9	ASSISTANT 8	5
76	2.6.2.10	ASSISTANT 9	0
77	2.7	MANAGER'S 8 OFFICE	12
78	2.7.1	MANAGER 8	1
79	2.7.2	ASSISTANT 1	1
80	2.7.3	ASSISTANT 2	5
81	2.7.4	ASSISTANT 3	5
82	2.8	MANAGER'S 9 OFFICE	39
83	2.8.1	MANAGER 9	4
84	2.8.2	ASSISTANT 1	5
85	2.8.3	ASSISTANT 2	5
86	2.8.4	ASSISTANT 3	5
87	2.8.5	ASSISTANT 4	5
88	2.8.6	ASSISTANT 5	5
89	2.8.7	ASSISTANT 6	5
90	2.8.8	ASSISTANT 7	5
91	3	SECTION 3	49
92	3.0	MANAGER'S 1 OFFICE	3
93	3.0.1	MANAGER 1	1
94	3.0.2	ASSISTANT 1	2
95	3.0.3	ASSISTANT 2	0
96	3.1	MANAGER'S 2 OFFICE	6
97	3.1.1	MANAGER 2	0
98	3.1.2	ASSISTANT 1	2
99	3.1.3	ASSISTANT 2	4
100	3.1.4	ASSISTANT 3	0
101	3.1.5	ASSISTANT 4	0
102	3.2	MANAGER'S 3 OFFICE	2
103	3.2.1	MANAGER 3	1
104	3.2.2	ASSISTANT 1	1
105	3.3	MANAGER'S 4 OFFICE	38
106	3.3.1	DAY PERSONNEL	3
107	3.3.1.1	MANAGER 4	1
108	3.3.1.2	ASSISTANT 1	2
109	3.3.1.3	ASSISTANT 2	0
110	3.3.2	SHIFT PERSONNEL	35
111	3.3.2.1	SHIFT MANAGER	5
112	3.3.2.2	ASSISTANT 1	15
113	3.3.2.3	ASSISTANT 2	15
114	3.4	MANAGER'S 5 OFFICE	0
115	3.4.1	MANAGER 5	0
116	3.4.2	ASSISTANT 1	0
test (staff)

Cell Formulas
Range	Formula
C7	C7	=IF($A$3<>1,SUBTOTAL(9,C8:C10),(C8))
C8,C114,C102	C8	=IF($A$3<>1,SUBTOTAL(9,C9:C10),SUM(C9:C10))
C11	C11	=IF($A$3<>1,SUBTOTAL(9,C12:C90),(C12+C16+C18+C24+C30+C36+C59+C77+C82))
C12,C110,C106,C92	C12	=IF($A$3<>1,SUBTOTAL(9,C13:C15),SUM(C13:C15))
C16	C16	=IF($A$3<>1,SUBTOTAL(9,C17),SUM(C17))
C18,C96,C60,C30,C24	C18	=IF($A$3<>1,SUBTOTAL(9,C19:C23),SUM(C19:C23))
C36	C36	=IF($A$3<>1,SUBTOTAL(9,C37:C58),(C37+C44))
C37	C37	=IF($A$3<>1,SUBTOTAL(9,C38:C43),SUM(C38:C43))
C44	C44	=IF($A$3<>1,SUBTOTAL(9,C45:C58),SUM(C45:C58))
C59	C59	=IF($A$3<>1,SUBTOTAL(9,C60:C76),(C60+C66))
C66	C66	=IF($A$3<>1,SUBTOTAL(9,C67:C76),SUM(C67:C76))
C77	C77	=IF($A$3<>1,SUBTOTAL(9,C78:C81),SUM(C78:C81))
C82	C82	=IF($A$3<>1,SUBTOTAL(9,C83:C90),SUM(C83:C90))
C91	C91	=IF($A$3<>1,SUBTOTAL(9,C92:C116),(C92+C96+C102+C105+C114))
C105	C105	=IF($A$3<>1,SUBTOTAL(9,C106:C113),(C106+C110))

 

[Fluff]

Duplicate Insert sum/subtotal formulae to sum values on a list

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).