Organization Chart Formula

[ShogunStealth]

I have simple spreadsheet with 3 columns needing to calculate a forth column. It represents an organization from the CEO to Mailroom Clerk. The organization could be more up to 3000 staff members (or more). The organization can have 30+ or more reporting levels deep, say from the CEO to Mailroom Clerk. Imagine a company organizational chart. The Columns in the sheet represent the Name of Staff (TEXT) / Unique ID (TEXT/NUMBER) / Parent (TEXT/NUMBER). The type or name of reporting level is not important it is more about the dependencies either directly or indirectly. The forth column is to represent the total number of direct and indirect reports. Therefore the CEO would Total # of Staff - 1. Therefore each of there direct reports which could be department heads / regional managers / supervisor / consultant will have their own total number of direct or indirect reports. If the CEO and Mailroom Clerk have the same name - John Smith this not the same person it based on their respective position within the organization. The list of staff may or may not be ordered or structured in any top down layout therefore any formula that relies on the data being ordered / structured is not desirable, ie the unique ID or the name of the staff may not be arranged from smallest to largest or alphabetical. Even though my linked sample is ordered this was built this way for ease of manually calculating the sample "Reports" column.

I prefer in sheet based formula rather than visual basic solution. I can see that solution is sum + compound/recursive formula but just cannot grasp the reclusive element, is it countifs??

Limitation - Excel 2016, so any later version of functions in excel and not suitable as I have to run it on work environment. I cannot download plugin like solutions either due to network/ firewall restrictions.

 

[ShogunStealth]

I have a need to also know the further detail for each staff member in a fifth column. How many levels of management are between themselves and the CEO.
If I reported directly to the CEO my # of levels = 0, but if I reported to departmental manager who then directly reported to the CEO my report levels = 1 ( I become a indirect report)

 

[ShogunStealth]

Here is a simple visual representation of the above data. If on sheet formula is not possible I willing to consider options in VBA, I just might need some help thought.

 

[ShogunStealth]

Simple.xlsx
	A	B	C	D	E
1	Name	ID	Parent	Reports	Report levels
2	Boss	1	1	12	0
3	Supervisor 1	2	1	4	1
4	Supervisor 2	3	1	6	1
5	Team Leader 1	4	2	3	2
6	Team Leader 2	5	3	2	2
7	Team Leader 3	6	3	2	2
8	Member4	7	6	0	3
9	Member5	8	6	0	3
10	Member6	9	5	0	3
11	Member7	10	5	0	3
12	Member8	11	4	0	3
13	Member9	12	4	1	3
14	Member10	13	12	0	4
Sheet1