Avatar of Kevin Smith
Kevin SmithFlag for United States of America asked on

Sum hours from another sheet in an Excel workbook, match by employee name...?

I have an Excel workbook with three sheets.  SHEET 1 keeps track of each day and the hours for a few employees on that day (each day of the year has a column) for a particular project manager...

so...

               9/1/2013    9/2/2013  9/3/2013

tommy     8                   6                 7
billy         6.5                8                  8
james      5                    4                 4

...and another sheet (SHEET 2) will have the same employees, but they might use them on different days.


               9/1/2013    9/2/2013  9/3/2013

tommy     2                   3                 8
billy         4                  4                 4
james      8                   4.5               2

I have another sheet (TOTALS) that looks the same, but I want the hours to total all hours for that employee for that day for both SHEET 1 and SHEET 2.  So the TOTALS spreadsheet would list all employees and their totals from other sheets.  So TOTALS would check SHEET 1 and SHEET 2 for Tommy and put 9 hours on 9/2/2013.

How do I do this?
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Kevin Smith

8/22/2022 - Mon
ASKER
Kevin Smith

Okay...I've got the SUMIF function working...

=SUMIF('Robert Morgan'!$F12:$F9194,"Mark James",'Robert Morgan'!H12:H9194)

...how do I do multiple worksheets?
ASKER CERTIFIED SOLUTION
Rob Henson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rob Henson

Also, I suspect you need to lock the row references in both sections of the SUMIF so that they don't change when you copy down the sheet:

=SUMIF('Robert Morgan'!$F$12:$F$9194,"Mark James",'Robert Morgan'!H$12:H$9194)+SUMIF('William Morgan'!$F$12:$F$9194,"Mark James",'William Morgan'!H$12:H$9194)

Thanks
Rob H
Rob Henson

Would you be averse to a change in format?

By having your grid format as it is, you have 365 columns of dates and however many rows for the number of employees, I will assume 200. Therefore you have 2 lots of 73,000 cells with potential for entry. Each one of those could have a potential for error. Likewise because you have the dates across 365 columns and the employees down 200 rows, excel assumes that all 73,000 of those cells are being used even if they are empty; potentially increasing the size of your file which may or may not be a problem for storage resource and/or recalculation process time.

How about having a database style format. You have 3 columns (possibly 4 - later!) and as many rows of data as you have entries.

The column headers would be:
  Date
  Employee
  Hours
The 4th potential column would be for Department(?) and would be used instead of having two sheets with input.

This style of data would then lend itself perfectly to using a Pivot Table to summarise the data on the TOTALS sheet.

I can be more specific if you so require.

Thanks
Rob H
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
Kevin Smith

Got me on the right track, thanks!