Joseph
This is a bit cumbersome but you could build a couple of arrays - one that houses the department number and the other that houses the count. If you do it this way, then you could do a match on the department array for the department number and if it is found, then extract the count from the count array.
Put an extra optional variable in the Leavers function. This will house the department number. If it is missing, then it will return the total count as it presently does. If the variable is present, then you can do a search on the department array, and if it is found then extract the number from the count array.
The function in C71 would be as it stands.
However, if you put it into C50, it would be =Leavers(C2:C44,B50)
Hopefully the following will give you an idea of what I mean
Sub ccc()
arrdept = Array(111, 132)
arrcount = Array(1, 2)
On Error Resume Next
holder = WorksheetFunction.Match(132, arrdept, 0)
If holder <> "" Then MsgBox arrcount(holder - 1)
End Sub
You can use the match function to find the position in a 1 dimensional array of an item. This can then be applied to another array.
Problem:
You have mulitple department numbers for a super department. One way to overcome this would be to use a lookup table to convert the dept number into the super dept number before putting into the arrays. You would also have to determine if an entry already existed and if so, then add additional entries.
Food for thought.
Tony