Here is a formula example from an HR database which calculates the
average Full-Time Equivalents. The row is the dept and the column is
the period.

=SUMPRODUCT((PosDeptNum=$A10)*(G$7<=PosEnd)*(PosSt art<G$8)*(((G$8<PosEnd)*G$8+(PosEnd<=G$8)*PosEnd)-((G$7<PosStart)*PosStart+(PosStart<=G$7)*G$7)-(PosStart<=G$7)*(G$8<PosEnd))/(G$8-G$7-1))

In order to "show my work" I would have to have a row for each
individual and then use Subtotal for the departments, or have a
different tab with the calculations for each individual, and then use a
SUMIF on the departmental tab.

There are new employees being added constantly, and employees
departments can change so the creation of the middle step would be a
manual process, or involve writing code, which I have also been asked
not to do, for auditing purposes (not everyone knows VBA so again they
can't see my work).

thanks!