Cost Driver calculation - Looking for formula/macro to calculate manpower cost alloca

I am working on calculating the cost drivers for my organisation.

We have 19 “Departments”, 11 “Head of the Departments” and around 1000+ “Employees”.

Due to Job rotation, Supervisory, Internal Expertise etc staff are often pulled / allocated to other department out of their parent department.

Form the staff attendance software we could get the monthly data of each staff worked in which department.

I have attached a sample database and my requirements.

I would appreciate if someone could help me getting the formula in the highlighted range of the attached worksheet.

Regards

Swastik

There's not enough info there to guess, and $570,000 looks wrong. You skipped C16:C18 in the CTC column.

GO ahead and manually fill out that yellow section. That's the only way we can have a hope of seeing what you think the logic should be.

I can suggest a D2 formula as:

=SUMIF(D$16:D$32,$B2,$C$16:$C$32)
...copied down.

Thank you JBeaucaire for your response.

Yes you are right. I did not calculate the CTC column correctly.

I have also filled up the expected result.

Revised sample data is attached.

Would very much appreciate your help.

Regards

Swastik

For the Department Allocation:

D2:
=SUMPRODUCT(($D$16:$O$32=$B2)*($C$16:$C$32/12))
copied down

I don't really understand the point of E:G calcs I'm afraid...

EDIT: perhaps you want:

E2:
=IF(ISNUMBER(SEARCH($C2,E$1)),$D2,"")
applied across E2:G7 ?

It would make sense to just store the names in E$1 and run a MATCH test as it will be more accurate and less open to generating spurious results.

Thank you so much for understanding my problem so precisely and providing me the solution.

My salute to you.

Regards

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

Thank you for providing the solution.

Be sure to EDIT your original post (#1), click Go Advanced and mark the PREFIX box [SOLVED].