Reporting Line Analysis

[crimsoneaglet]

This is either a very easy question or a very hard one, I'm not sure which.

What I have is a list of people and then alongside that, a list of the people who that person reports to. What I want to know is how many people cumulatively report into each person, including those who report into those who report into them. Example below where because Person B, C and D report to Person A, and everyone else reports to them, the number of reports Person A has is actually 25, not 4.

Employee	Manager
Person A	Super Boss
Person B	Person A
Person C	Person A
Person D	Person A
Person E	Person A
Person F	Person B
Person G	Person B
Person H	Person B
Person I	Person B
Person J	Person B
Person K	Person B
Person L	Person B
Person M	Person C
Person N	Person C
Person O	Person C
Person P	Person C
Person Q	Person C
Person R	Person C
Person S	Person C
Person T	Person C
Person U	Person D
Person V	Person D
Person W	Person D
Person X	Person D
Person Y	Person D
Person Z	Person D

<tbody>
</tbody>

Does anyone have a solution? This is driving me mad.

Thanks!

 

[WarfritLive]

I spent way too long trying to do this with a long formula.
Just do this:

-In column B paste your Employees
-In column C paste your managers
-In cell D2 write the formula =VLOOKUP(C2,$B:$C,2,FALSE)
-Double click the thing to autofill the formula down
-Click and drag the thing to autofill to the right. Keep going until the rightmost column is N/A's
-Somewhere else make a column of the managers you want to tally (as pictured on the right).Mine is in H
-In the cell next to the manager name use the formula =COUNTIF(C:G, H2)
-Drag it down so it tallys all your managers

Boom done.
Work smarter not harder

Edit: Obviously for the tally formula you need to use whatever column is appropriate

 

[crimsoneaglet]

Perfect solution.

Thanks!