Calculate number of roles in an organizational chart given under excel list form

Hi everybody,

I am struggling with a problem which seems to be easy but it is revealing much trickier than I thought.

I have an org chart (see excel attached) with managers that have sub-managers and several people with technical or sales roles. The goal is quite simple : calculate the number of people in each role for a given manager (that number should include everybody below) at all levels. The problem is that I have everything under an excel list as it is showed in the input and those are more than 2500 lines which don't enable me to draw an org. chart and do it by hand. And I also have more than 4 levels, with some people being "dead-ends" while peer colleagues have a vast ramification below.

I have tried to use index match to catch all the sub-managers for a given manager but I am stuck as index match only gives the first one and not all of them. I am sensing that we could maybe do it the other side (for each person, put his/her manager, and do this in several columns to go up one level each time until I "hit" the highest manager, for some of the people, the latest columns will be empty as they will be closer to the big boss) but I am having a rough time coming up with a simple way of doing it.

Thank you very much for your kind help in advance,

Xenthys

Mmmm. I'm not happy with this. It's not as neat as it should be. I suspect it'll be unworkable in your real situation. However, I have got close....