Organisational Hierarchy - Cumulative Totals

[andrenculleton]

Hi,

I am currently trying to map employee headcount to the organisational hierarchy.

Example:

Department A - 2 Employees
Department B - 5 Employees
Department C - 7 employees
Department D - 4 Employees
Department E - 5 Employees

Department E reports to Department C
Department D reports to Department A
Deparment C reports to Department B
Department B reports to Department A
Department A is top of pyramid

Is there a formula that will cumulate at each leavel the number of employees based on the reporting hierarchy? I can get close with Sumif + vlookup, but wondering if anyone has a better way. Any information greatly appreciated.
Cheers,
Andrew

 

[nbrcrunch]

The way I have been successful at this is by using a numbering system that is inherantly in hierarchy form.

If Dept A is number 100 then E would be 110
Individuals in those depts would be deicmals

100.01 The Big Boss
100.02 CFO
100.03 IT CIO

110.01 VP of busines
110.02 VP of finance
110.03 VP of Information Technology

You get the idea.

As far as doing it your way, no I can't think of an easy way to do that.

 

[andrenculleton]

Thank you. I will give it a go.

Cheers,
Andrew

 

[fairwinds]

Hi,

If your hierarchy is "sorted", I believe a SUMIF should do the job.

D2: =SUMIF(C3:$C$7,A2,D3:$D$7)+B2

dragged down.

Book1
	A	B	C	D	E
1	Dept.	Employees	Reports to	Acc employees
2	Department A	2		23
3	Department B	5	Department A	17
4	Department C	7	Department B	12
5	Department D	4	Department A	4
6	Department E	5	Department C	5
7
Sheet1