3

I am having two sheets Sheet1 & Sheet2, Sheet1 having four columns like EMP No, Project Number, Month & Working Days.

In the sheet2 I have same four column but some employee will be worked in two projects for that month, For example: If Emp No, project number & month matches, then 4th column value for that employee should be populated. And I am doing this by referring the cells in both the sheets.

Both sheets having the same order as mentioned in the below.

Column A = Emp No,
Column B = Project Number
Column C = Month
Column D = Working Days

=SUMIFS('Sheet2',$D$2:$D$10000,$C$2:$C$10000,"="&M2,$B$2:$B$10000,"="&C2,$A$2:$A$10000,A2)
Gyana Prakash
  • 79
  • 2
  • 3
  • 13

2 Answers2

2

I gather your aggregation happens in Sheet2 so the formula should be:

=SUMIFS(Sheet1!D:D,Sheet1!A:A,Sheet2!A1,Sheet1!B:B,Sheet2!B1,Sheet1!C:C,Sheet2!C1)

If your aggregations happens in Sheet1 just go with this one:

=SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!A1,Sheet2!B:B,Sheet1!B1,Sheet2!C:C,Sheet1!C1)
zipa
  • 27,316
  • 6
  • 40
  • 58
  • Thank you so much, This formula works perfect, From yesterday i was trying with many formulas nothing worked for me, Finally you have fixed my issue in a short time, Once again thank you....!! – Gyana Prakash Feb 22 '18 at 11:31
  • 1
    You are welcome. Please accept the answer and upvote if you can :) – zipa Feb 22 '18 at 11:33
1

If I've understood correctly, Sheet2 can contain multiple lines for employees per project per month? So, for example, EMP No 1 could be listed for Project 1, in the month of January 5 times, with working days each time equalling 1, and Project 2, int he month of January 2 times, with working days each time equalling 1.

Your Sheet1 worksheet, intends to list all employees, projects and months uniquely in order to total the days worked on each project each month? Based on the example, EMP No 1 would have the following displayed:

enter image description here

What I would suggest, is providing you have the unique list in Sheet1 already set up, enter the following formula into cell D2 (Working Days) and then copy down. I have assumed 100 entries on Sheet2, so you will need to amend the Sheet2! ranges in the formula to suit your data.

=SUMIFS(Sheet2!$D$2:$D$101,Sheet2!$A$2:$A$101,Sheet1!$A2,Sheet2!$B$2:$B$101,Sheet1!$B2,Sheet2!$C$2:$C$101,Sheet1!$C2)

S. Higgs
  • 56
  • 2