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?
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?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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,"Mar
Thanks
Rob H
=SUMIF('Robert Morgan'!$F$12:$F$9194,"Mar
Thanks
Rob H
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
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
ASKER
Got me on the right track, thanks!
ASKER
=SUMIF('Robert Morgan'!$F12:$F9194,"Mark James",'Robert Morgan'!H12:H9194)
...how do I do multiple worksheets?