I'm working on a team project where I've been asked to combine data from multiple worksheets(with different attributes), and put them inside a Master Sheet.

At this moment,

Master Sheet has the date, team leader, Employee name, Employee Caller if(from Phone Sheet), Employee Email id(from Email Email id), and Employee if(from Phone Sheet).

Phone Sheet has Employee Caller id, date, and the duration of calls.

Email Sheet has Employee Email id, date, and the time spent on each email.

Schedule Sheet has Employee id, time spent on the phone, and time spent on email.

I tried using the data model to combine all the sheet using a composite key for all the 3 different ids, but instead of making it more complex, I want to use the sumifs function to do the same thing, in a more basic manner.

  • 1
    This is very unclear.  (1) You talk about “Employee if” and “Employee Caller if”.  Are these meant to be “Employee ID” and “Employee Caller ID”?  (2) You say that “Employee if” and “Employee Caller if” come from the Phone Sheet, but the phone sheet has only one id value.  (3) You don’t say anything useful about what result you want. … … … … … … … … … … … … … … … If you want help, describe the data that you have more explicitly (e.g., do the sheets have one row per employee?  if not, what?), give some example data, including results, try to solve it, and show us where you got stuck. – Scott Mar 20 '19 at 1:10

I don't believe a sumifs will do what you want. But if you are allowed to restructure your sheets, it seems like you could merge the phone and email sheet into one. You then add two columns 1) for the employee name and 2) phone/or email. Then just do a pivot on this new table to get your master sheet. You could do away with the schedule sheet as it appears to be redundant.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.