2 Tables and a formula

Hi,

I'll try and explain below what I am trying to do and hopefully there is a way to properly do it.

I have attached a copy of the table for reference details.

I have a table with employee Name, MV Allowances,. Monthly Wage, OnCosts and Total charge.
I have a second Table with Job Number, Employee Name and Time % spent on said different jobs.

What I want to do is in column A - calculate and sum the cost which needs to be oncharge to each individual Job (for all employees associated with that job) based on the % of time that they spent on the job.

IE for Job 2713
Michael Longlend spent 15% of his time on it and Phil Warpick spent 4% of his time on it.
So total on charge would be 15% of Michael's Total Charge (ie 15% of $17,774.06 - $2,666.11) + 4% of Phil Warpick's Total Charge (ie 4$ of $16,545.76 - $661.83) - giving a total oncharge of $3,327.94.

I did consider a sumif formula but that would be too many variables and there is a potential for the number of employees in the list to grow.


Hope this is detailed enough.

Ta for the help.