# Calculate Total FTE for each manager in a hierarchy

#### stevedjswitch

##### New Member
Apologies if this is a duplicate, but I couldn't find this exact question asked elsewhere;

I have a worksheet which shows standard organisational hierarchy, i.e. a column of employees and a column with their unique IDs, plus a column which shows their managers' ID (each manager ID is also an employee).

I also have a column which shows for each employee their employment percentage or FTE (full time equivalent) - i.e. full time = 100%

What I'd like to do is display the total sum of FTE's that each managers has within their control, both directly reporting to them, and indirectly, through people who report to them, who also manage people. There's no max to the number of potential layers.

I can get to a count of employees per manager through a UDF which sets up a recursive count, but something similar which will carry out a sum function is eluding me.

I guess another UDF would probably be the best option, but if there's a non-VBA version that works better, I'd be keen to hear it.

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

##### Active Member
I think the limitless depth is the issue from a standard formula perspective.
Unless you could use a helper cell to give you an ultimate manager, or an array, as you'd want to know at each level, I think you're going to struggle doing it "in sheet".

#### oldbrewer

##### Well-known Member
 employeeid level FTE M12 M13 id1 M121 75 percents 435 735 id2 M131 65 19 EMPLOYEES employees 7 10 id3 M131 90 FTE 4.35 7.35 id4 M1 100 BIG BOSS = M1 id5 M121 40 id6 M121 50 2 DIRECT REPORTS id7 M131 75 id8 M131 75 M12 M13 id9 M12 100 id9 id13 M1 id10 M121 75 id11 M131 50 M12 M13 id12 M131 50 direct direct 1.00 1.00 id13 M13 100 reports reports 4.35 7.35 id14 M121 35 5.35 8.35 id15 M121 60 id1 id2 id16 M131 80 id5 id3 id17 M131 80 id6 id7 13.70 id18 M121 100 id10 id8 id19 M131 100 id14 id11 id20 M131 70 id15 id12 id18 id16 id17 id19 id20 is this the sort of thing - will take an hour or so to set up but then it is done

<colgroup><col><col span="8"><col><col span="3"></colgroup><tbody>
</tbody>

#### stevedjswitch

##### New Member

Hmmm. I think you may be right regarding standard formula.

I think the answer must be a user defined function, similar to one I found to get the total headcount, but I can't make my stupid brain think of it!

#### viktiw

##### Board Regular
Hi steve,
i believe you have a data as shown below
 Employee Name Employee Id Manager's Name Manager's ID FTE % Mark 11 Harry 21 90% Tom 12 Morgan 22 100% Jim 13 Morgan 22 80% David 14 Harry 21 40% Vik 15 Harry 21 50%

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>

And i belive you are looking for number of employees working under Mr. Harry and Mr. Morgan, right ?

A simple pivot table can give you these details and others.
Create a pivot table with Manager's name as Row Field and Employee Id/Name as data field and set it to Count of Employee id/Name.

It will give you that there are 3 Employees working under Mr. Harry and 2 Employees working under Mr. Morgan.

Hope i have understood your question correctly.
Let me know if i didn't.

Thanks,
vik

#### stevedjswitch

##### New Member

Hi Vik

That's exactly how my data would be structured, but not quite what I'm looking for as a solution. In your example above, let's assume that there's a line added in which Mr Morgan also reports to Mr Harry. What I'm looking for is a method to calculate the total sum of the FTE reporting to each manager. So the answer for Mr Harry would be the total sum of the FTE all his direct reports plus the total sum of FTE of people who report to Mr Morgan.

This is a simplified way of explaining it, as in reality, some of those people who report to Mr Morgan, may also be supervisors themselves, so the complexity is in effectively recreating an org hierarchy on the fly and then counting or summing down from each level to the bottom.
Hi steve,
i believe you have a data as shown below
 Employee Name Employee Id Manager's Name Manager's ID FTE % Mark 11 Harry 21 90% Tom 12 Morgan 22 100% Jim 13 Morgan 22 80% David 14 Harry 21 40% Vik 15 Harry 21 50%

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>

And i belive you are looking for number of employees working under Mr. Harry and Mr. Morgan, right ?

A simple pivot table can give you these details and others.
Create a pivot table with Manager's name as Row Field and Employee Id/Name as data field and set it to Count of Employee id/Name.

It will give you that there are 3 Employees working under Mr. Harry and 2 Employees working under Mr. Morgan.

Hope i have understood your question correctly.
Let me know if i didn't.

Thanks,
vik

#### stevedjswitch

##### New Member
Hi oldbrewer. Thanks for answering, however I'm not totally sure I understand your solution. Are you suggesting using helper tables to pull out and summarise the direct reports for each supervisor?
 employeeid level FTE M12 M13 id1 M121 75 percents 435 735 id2 M131 65 19 EMPLOYEES employees 7 10 id3 M131 90 FTE 4.35 7.35 id4 M1 100 BIG BOSS = M1 id5 M121 40 id6 M121 50 2 DIRECT REPORTS id7 M131 75 id8 M131 75 M12 M13 id9 M12 100 id9 id13 M1 id10 M121 75 id11 M131 50 M12 M13 id12 M131 50 direct direct 1.00 1.00 id13 M13 100 reports reports 4.35 7.35 id14 M121 35 5.35 8.35 id15 M121 60 id1 id2 id16 M131 80 id5 id3 id17 M131 80 id6 id7 13.70 id18 M121 100 id10 id8 id19 M131 100 id14 id11 id20 M131 70 id15 id12 id18 id16 id17 id19 id20 is this the sort of thing - will take an hour or so to set up but then it is done

<colgroup><col><col span="8"><col><col span="3"></colgroup><tbody>
</tbody>

yes

#### stevedjswitch

##### New Member
OK, this is an example of the output I'm after. The Headcount Managed is calculated using a UDF which uses a recursive count, but I can't get my head around how to calculate the FTE Managed.

Excel 2010
ABCDEFG
1Employee NameEmployee IdFTE %Manager's NameManager's IDHeadcount ManagedFTE Managed
2Mark1190%Morgan213225%
3Tom12100%Morgan2200
4Jim1380%Morgan2200
5David1440%Harry2100
6Vik1550%Harry2100
7Morgan22100%Harry216495%
8Harry21100%9685%
9Crystal1650%Mark1100
10Brian4575%Mark1100
11Helen23100%Mark1100
12
13Total Count = Total FTE Sum =
1410785%

</tbody>
Sheet1

Replies
1
Views
183
Replies
22
Views
373
Replies
3
Views
87
Replies
1
Views
71
Replies
7
Views
447

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,519
Messages
5,832,230
Members
430,117
Latest member
RRattle

### 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.

### Which adblocker are you using?

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