Sum product for all the months if 2 tables match

Question

So I have this issue, I have two tables one is employees, and another one is the projects.

Employees Table:

Year Name Type Jan Feb

2018 Kevin Salary 5000 2000

2018 Kevin Insurance 200 400

2018 Alex Salary 3000 4000

2018 Alex Insurance 300 400

Projects Table

Year Project_Name Employee_Name Jan_Hours_Worked Feb_Hours_Worked

2018 Apple Alex 7 5

2018 Apple Kevin 5 0

2018 LG Kevin 0 3

Now I am creating a result list of all the projects and costs recurred for them, what I need is for each project in Table 2 to find which employees are involved and then use that to find related costs for the employee from the Table 1 and calculate total costs for that project.

(e.g for project LG, I have Kevin working on that in Feb,for him company paid 4400(salary+insurance) in Feb and the costs recurred for the LG project would be 4400 divided by hours spent on the project which Kevin in total spent 3 hours; e.g.2 for the project Apple it would be the same but sum of Kevin's and Alex's costs from Jan and Feb, so Kevin: 5200/5 + Alex:3300/7 + 4400/5)

Now I have the formula to calculate this for 1 months which is something like this

=SUMPRODUCT(SUMIFS(Employees[Jan], Employees[Name],Project[Employee_Name], Employees[Year], 2018 )/Project[Jan_Hours_Worked],--(Project[Project_Name]=K14))

I need to find how to get the yearly result per project without repeating the formula 12 times, also with this formula, i get div to 0 error when an employee didn't work on particular months, so that needs to be sorted somehow. Any Help?

Answer 1

I suggest you to change how you store your data. If you can make some minor changes, then you can have an easy way to get the information you want, and also a Pivot Table with a summary of cost recurred for each proyect and which employee generated that cost.

IMPORTANT: For this answer to work, you must make sure that every Employee's Name is UNIQUE. If not, adapt the example trying to create an Employee's ID or something.

Also, please, note i got a spanish version of Excel, so screenshots are in spanish but I will translate formulas :)

Ok, first of all, I changed the design of your table Employees. Creating a column for each month is kind of annoying. Use just a column to get the month. You can type the month in a cell just like 01/2018 and Excel will change it instantly to format mmm-yy (Jan-18)

This is how your Employees table should look:

The column TOTAL COST is just a sum of SALARY + INSURANCE. If you have any other concept, just add it as a column and modify the TOTAL COST COLUMN to include it.

Second, the table Project, I think it should be like this:

The column Employee Cost has an Array Formula.

IMPORTANT: Array formulas are inserted pressing CTRL+SHIFT+ENTER

The formula is (I used same names for tables, so copy-pasting should work for you):

=INDEX(Employees;MATCH(Project[[#This row];[Employee_Name]]&Project[[#This row];[Month]];Employees[Name]&Employees[Month];0);COLUMN(Employees[[#Headers];[TOTAL COST]]))

If you typed the formula right, you should see { at start and } at end.

The formula in Cost Recurred to Project is just a division of Employee Cost / Hours. Added an IFERROR when the hours worked are 0, then show 0.

=IFERROR(Project[[#This row];[Employee Cost]]/Project[[#This row];[Hours]];0)

And last step, your Pivot Table. Create one and organise it to get the sum per hours and month and proyect you want. You can get one like the one below:

As you can see,e.g. for project Apple, you can see that total cost is 2.391,43 but also you can see the cost of each Employee. Pretty cool I think.

I really hope you can modify the design of your data, because Excel is designed to work going down (I mean using rows) more than using columns. Excel 2007 got more than 1 million of rows and just around 16.000 columns, so it's designed to work vertically.

Hope this helps, or at least, give to you a clue of how to proceed :)