-1

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

Table Employees:

Year Name Type       Amount    
2018 Kevin Salary    5000    
2018 Kevin Insurance 200    
2018 Alex  Salary    3000    
2018 Alex  Insurance 300

Projects Table

Year Project_Name  Employee_Name  Hours_Worked    
2018  Apple        Alex                7    
2018  Apple        Kevin               5    
2018  LG           Kevin               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 the Table 2 find which employees are involved and then use that information to find related costs for the employee and calculate total costs for that project.

(e.g for project LG, I have Kevin working on that,for him company paid 5200(salary+insurance) and the costs recurred for the LG project would be 5200 divided by hours spent on the project, for the project Apple it would be the same but sum of Kevin's and Alex's costs recurred)

I have tried several things but didn't work, is there a way to lookup project name in one table and use the returned value(employees) to get the related costs from another Table ?

Community
  • 1
  • 1
Kev
  • 3
  • 4

1 Answers1

0

Your formula would use SUMPRODUCT and SUMIFS:

=SUMPRODUCT(SUMIF(B:B,$H$2:$H$4,D:D)/$I$2:$I$4,--($G$2:$G$4=K2))

The references to the second table must be limited to the data set.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Thanks Scott, what you mean by "The references to the second table must be limited to the data set."? – Kev May 08 '18 at 20:02
  • Looking my formula, there are some references that are full columns and others that are not, because of the way the formula evaluates we need to limit some of the references to limit the number of iterations. Those that are full column (References to the first table) can remain full column without detriment. Those that are limited (References to the second table) must remain limited to the actual data set, do not use full column references. This part will iterate one by one through all the cells and if you use full columns it will do over 3 million calculations per instance. – Scott Craner May 08 '18 at 20:06
  • Thanks a lot this seems to work except I use a tables with column names and my references doesn't work properly : so the code is something like this now =SUMPRODUCT(SUMIF(Employees[Employee], Project[Employee], Employees[Jan])/Project[Jan],--(Project[Name]=K14)), could you please let me know how to do the correct referencing to the cells? Also one more thing, I do this calculation for each month (I have 12 separate columns in both tables for each months in place of the amount and hours worked) is there any way I can also sum all 12 months together? – Kev May 08 '18 at 20:35
  • the formula works for me if I make it tables and name the columns correctly. As to the full year thing that is a new question and will change the formula significantly. – Scott Craner May 08 '18 at 21:16
  • Yes thanks, I was thinking off just adding up this formula 12 times for each months(not so efficient probably), also I found the problem, since I am doing this for the whole year, there is a chance that in some months employee didn't work on the project, therefore, in division, it gives me an error. Any help on this? – Kev May 08 '18 at 21:29
  • This question has been answered. You are asking a whole new question. Please mark this as correct if it answered this question and ask a new one with your new requirements. – Scott Craner May 08 '18 at 21:35