Organisational Hierarchy - Cumulative Totals

andrenculleton

New Member
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

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.

Thank you. I will give it a go.

Cheers,
Andrew

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
ABCDE
1Dept.EmployeesReports toAcc employees
2Department A223
3Department B5Department A17
4Department C7Department B12
5Department D4Department A4
6Department E5Department C5
7
Sheet1

Replies
6
Views
242
Replies
1
Views
92
Replies
9
Views
295
Replies
6
Views
320
Replies
7
Views
189

1,203,541
Messages
6,056,006
Members
444,840
Latest member
RazzelDazel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back