Pivot Table - Determining Total Count

Hi,

Need some quick help. Attaching an excel in which I would like to do a count of all the people reporting up to Bob Smith. For example, Bob Smith has 8 direct reports, but then those reports have people reporting to them (For example John Smith reports to Bob smith, but Lebron James and Michael Jordan report to John Smith and then Phil Jackson reports to Michael Jordan). At the end, I want a count of all the people reporting to Bob. Is there a method to do this that is also easily replicate and repeatable for a much larger data set? Right now, I'm just using a PivotTable and counting for each particular boss, but that is time consuming.

Also, yeah I took some athlete names

Thanks!

Take a look at the attached worksheet pivot table and see if it is what you want.

The grand total is right. I'm not sure if this is a product of the data and the fact that all names linked to Bob Smith, but if I ever to apply it to a larger set, how would I do that?

The pivot table is generated from your data. It is very easy to make this applicable to a much larger dataset. Suggest you put your data in an Excel table. That way when you add or delete from the table, the pivot table will be updated upon clicking on refresh. With Excel 2013 you can add slicers to make it more visible what names have been selected. Approximately how many names are you considering?

Hundreds. Is there a way to automatically go down the chain of bosses or do you have to manually add each one?

Could you post a small sample (20-30) rows of desensitized data, so we can see how your data is laid out.