Calculate and Split amount based on Salary and hours worked

[excelnovice2000]

Hi Guys
Can I get some more help in calculating and splitting

I want to find the amount based the employee salary and the hours worked

Example: If Employee-1 has worked 8, 10, 8, and 5 on 4 projects and I want split the Employee-5 Salary based on the hours worked on the 4 jobs

I got the amount highlighted in orange by taking 8 hours / 31 hours * 4750

emp tab has employee salary
hours tab has employee hours worked on different projects

Desired result is in the amt tab highlighted in green

Again thanks for all your help.

 

[bosco_yip]

Try,

In F4, formula copied down :

=AGGREGATE(14,6,hours!F$4:F$13/(hours!B$4:B$13=B4)/(hours!D$4:D$13=C4),COUNTIFS(B$4:B4,B4,C$4:C4,C4))/SUMIF(hours!B:B,B4,hours!F:F)*VLOOKUP(B4,emp!B:D,3,0)

 

[excelnovice2000]

Try,

In F4, formula copied down :

=AGGREGATE(14,6,hours!F$4:F$13/(hours!B$4:B$13=B4)/(hours!D$4:D$13=C4),COUNTIFS(B$4:B4,B4,C$4:C4,C4))/SUMIF(hours!B:B,B4,hours!F:F)*VLOOKUP(B4,emp!B:D,3,0)

View attachment 73701

Thanks Bosco. Really appreciate your help. The formula worked great.