Sum Index/Match results across multiple sheets

Hi,

I'm trying to do what I imagine is a fairly common task and summarise predicted staff costs across a number of jobs on a weekly basis.
The job forecasts are in individual sheets in a standard format, with w/e dates in cells D31:D136, and staff names running horizontally in cells E9:AZ9.
Cells E31:AZ136 then contain the weekly cost per staff member.

I have a summary tab where I have the same w/e dates, and all staff names along the top, and am trying to total the weekly cost per staff member across all jobs. Where Im struggling is that not all staff work on each job, and some jobs may be dormant so do not have any staff/cost details entered. I've defined the job list in a named range ("SCHEMES") to enable an INDIRECT function in the formula, but cant get my head around how to write it so that a) it keeps looking past the first scheme, and b) doesn't stop if it returns an error because there's no staff/scheme.

I've attached a simplified example workbook and if anyone can help I'd be grateful!!

Cheers

Further to JeteMc's post:

If you add sheets named "First" and "Last" at the beginning and end of your data sheets then the formula becomes

=SUM(First:Last!E31)

so you can add/delete sheets with no change to the formulae.

Thanks for the replies. I've realised my example workbook wasn't the best...!

Staff vary from job to job, as can the order they are listed in on different tabs, so the formula needs to look up the staff name from the Summary sheet.

Revised example attached.Test Resource 2.xlsx

Thanks

I have searched everywhere and I cannot find an example of the solution to your problem You can use individual SUMPRODUCTS i.e. Sheet by sheet and sum these

(SUMPRODUCT (sheett1 ...) + SUMPRODUCT (Sheet2 ) .....

but there does not appear to a way of using INDIRECT when the ranges include both rows (dates/amount) and columns (Staff).

Here is a VBA-based solution.

Please Login or Register  to view this content.

Note: I removed formulas from "Design" column in "Scheme List" tab (needed blanks to check end of entries!)