Summing over multiple sheets

[thespasticone]

Hi all, back again!
So attached is a simplified version of a sheet I am working on; basically I need to sum up the hours worked and sales for each individual in the weekly summary page, and I am at a loss as to how to do this.

Thanks!

[southward]

Hi,

While it is possible to do what you are asking, it is more work and trouble in the long run do to its complexity.
I suggest build and enter your data in a data entry format.
From the raw data you can more easily and effectively generate reports.

See attached for example using your spreadsheet.
There are two new sheets.
Data Entry and Report & Summary.

The Report & Summary uses a Pivot table to do what you are looking for.

Hope this is helpful.

Cheers

[FDibbins]

While I agree that using 1 sheet for ALL data is often the best way to capture data (makes summaries so much simpler), I also understand that is not always possible. With that said, see if this approach will give you what you want.

1. Create a list of all your sheets and give that list a range name (I used Tabs for the name)
2. use this formula, adjust the ranges as needed...
=SUMPRODUCT(SUMIF(INDIRECT("'"&tabs&"'!A2:A4"),$A2,INDIRECT("'"&tabs&"'!C2:C4")))
Change that last range C2:C4 to D for teh hours