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...


               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?
Kevin SmithAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin SmithAuthor Commented:
Okay...I've got the SUMIF function working...

=SUMIF('Robert Morgan'!$F12:$F9194,"Mark James",'Robert Morgan'!H12:H9194) do I do multiple worksheets?
Rob HensonFinance AnalystCommented:
Simplest would be to duplicate that SUMIF for the second sheet:

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

Changed name of second sheet to William Morgan as example.

This assumes the columns on both sheets have the same dates. If not we could come up with a combination of INDEX and MATCH formulas.

To be more flexible, you could also change the "Mark James" section to a cell reference containing the name. Remember to lock the column reference as you have with the references to $F12:$F9194

Rob H

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
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)

Rob H
Rob HensonFinance AnalystCommented:
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:
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.

Rob H
Kevin SmithAuthor Commented:
Got me on the right track, thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.