We help IT Professionals succeed at work.

Calculate a total from worksheets

Software Squirrel
Software Squirrel asked
on
I have a timesheet workbook. On each sheet, it totals the hours.  I want to take the first sheet and aggregate all the totals from the same field in all the sheets.  I wastimesheetQuestion.png looking to get total hours for all weeks and put it on the first sheet.  See Image.
Comment
Watch Question

View Solutions Only

Finance Analyst
BRONZE EXPERT
Commented:
Assuming all sheets have the same layout and have the total for each week in cell I19 on each sheet, you can do a three dimensional SUM but you will have to use a custom format to show as total hours.

=SUM('Feb 10:Mar 16'!$I$19)

Custom format:  [hh]:mm

The [ ] around the hours will tell excel not to convert hours over 24 to days.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Expand this to include every sheet.

=SUM(Sheet2!I19+Sheet3!I19)
Rob HensonFinance Analyst
BRONZE EXPERT
Commented:
Another assumption, I guess you will be adding more sheets as the weeks go by and you will need them included in the total.

If so, after the last sheet insert a blank sheet and call it Last and (optional step) hide it.

Then amend the formula above to:

=SUM('Feb 10:Last'!$I$19)

When new sheets are inserted ensure they are inserted before the Last sheet. The reason for the optional step of hiding the Last sheet is that when a sheet is selected and you click the + button to insert a sheet it will be inserted after the active sheet. If the Last sheet is hidden it can't be selected to have the new sheet inserted after it.
Rob HensonFinance Analyst
BRONZE EXPERT
Commented:
Another reason for hiding the Last sheet:

If you use the Right click on the tab and select the 'Move or Copy' and tick 'Create a Copy' to create a new sheet, the Last sheet won't be listed as an option for positioning the new sheet. The 'Move to End' option will put the new copied sheet before the Last sheet.