Unable to count numbers in a hierarchy where links are only indirectly established

[Marcos Aristotelous]

Hi - I'm looking for a formula that will allow me to add up the number of staff reports in an organisational hierarchy where I have the number of direct reports for each layer of management and who the next layer reports up to but no data to directly link teams downwards by more than a single level...so I'm looking to be able to calculate the number of staff in each team across several management layers...please see attached sheet....

Hope that makes sense

[Pete_UK]

Put this formula in I3:

=IF(D3="Level 1",B3,IF(D3="Level 2",G3,IF(D3="Level 3",INDEX(G:G,MATCH(INDEX(B:B,MATCH(G3,B:B,0)),B:B,0)),IF(D3="Level 4",INDEX(G:G,MATCH(INDEX(G:G,MATCH(INDEX(B:B,MATCH(G3,B:B,0)),B:B,0)),B:B,0))))))

and copy down to the bottom of your data. This will give you the name of the Level 1 manager for each member of staff. Then in J3 you can have this formula:

=SUMIF(I:I,B3,E:E)

which only needs to be copied down to J10 (i.e. the bottom of the Level 1 managers.

Hope this helps.

Pete

[Marcos Aristotelous]

Hi Pete - many thanks. That worked fine and I'm very grateful. Could I ask whether it would be possible to complicate it a tad? Would be possible to concatenate the first and last names in order to cover the scenario where more than one manager has the same surname? Separate ask - would you be able to extend the formula so that it captures team size for staff further down the organisation eg wherea Level 3 manager has a team of Level 4 staff working for them?

[Pete_UK]

There often is a follow-up question or two ...

It should be possible to do both those things - can you attach another sample workbook which has examples of these two new variations?

Pete

[Marcos Aristotelous]

Hi - have created a second template that I hope clearly articulates the ask

[Marcos Aristotelous]

staff org second example.xlsxAttachment 276406

Can you open this file?