Which formula for splitting the total cost between departments?

Hi everyone,

Imagine that you would like to register the amount by employee of his expenses, but each employee work for several departments, so you want to split the amount by employee between these departments. How can I do it? Which formulas should I use?


Employee costs by department.jpg


For example, I would like to register 100$ as total amount for Mark, and that automatically this amount would be splitted between his 4 departments. The same with the other employees.


Extra info:The original table is much longer and I should use it for create also a PivotTable to show the cost by Department.


Thank You very much,

Cost of Employees by department.xlsx

Can you attach a sample of your sheet as an Excel sheet, rather than as a screenshot?