Sum a range of cells in multiple worksheets that meet two criteria

Hi forum,

I log time spent on different tasks across multiple projects (tasks listed in columns, projects listed in rows).
Each new year, I create a new worksheet that tracks the same information.
As projects start and finish, they appear and disappear in the yearly worksheets.

I'm trying to create a formula on a summary page that adds all hours spent on a given project and a given task.

The formula I'm trying to use is:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&B69:B78&"'!Q:BD"),INDIRECT("'"&B69:B78&"'!Q7:BD7"),B8,INDIRECT("'"&B69:B78&"'!C:C"),A9))

Where:

B69:B78 is a list of the worksheet names
Q:BD are the columns that have the hours logged in them
Q7:BD7 are the task names
B8 is the task to check against
C:C is the column that has the project names in it
A9 is the project to check against

However, it returns the #VALUE error.

Any help would be much appreciated!

Thanks,
U

Let's forget about the multiple sheets for now (for troubleshooting purposes), and just look at the basic formula without the indirect.
You have
=SUMPRODUCT(SUMIFS(SomeSheet!Q:BD,SomeSheet!Q7:BD7,B8,SomeSheet!C:C,A9))

That syntax isn't going to work.
Sumifs requires the SumRange and CriteriaRanges to be of equal dimensions.

It looks like you're trying to find the column to sum based on finding B8 in SomeSheet!Q7:BD7
Try that like this

=SUMPRODUCT(SUMIFS(INDEX(SomeSheet!Q:BD,0,MATCH(B8,SomeSheet!Q7:BD7,0)),SomeSheet!C:C,A9))

Make sure that works correctly for 1 specific sheet.
Then put the indirect back in.

Okay, I'm getting stuck when I try to enter the INDIRECT function.

The basic function without INDIRECT gives me all the right values on worksheet 2017:

=SUMPRODUCT(SUMIFS(INDEX('2017'!Q:BD,0,MATCH(H8,'2017'!Q7:BD7,0)),'2017'!C:C,A22))

But when I try and generalise it to all worksheets, it returns the #VALUE error:

=SUMPRODUCT(SUMIFS(INDEX(INDIRECT("'"&B69:B78&"'!Q:BD"),0,MATCH(H8,INDIRECT("'"&B69:B78&"'!Q7:BD7"),0)),INDIRECT("'"&B69:B78&"'!C:C"),A22))

Where B69:B78 in the current sheet is a list of all worksheets.

Any suggestions?

Honestly, I don't know if you're going to be able to do that.
You may have to have 1 cell for a separate sumifs for each sheet, then sum those.

Yeah, I'm finding myself getting bogged down a bit. I've tried a slightly different tack though:

=SUMPRODUCT(SUMIF(INDIRECT("'SomeSheets'!$C:$C"),$A8,INDIRECT("'SomeSheets'!Q:Q")))

This works fine as long as the sum range remains within the Q:Q column, however for some sheets it might be U:U or even AA:AA. What I need to work out is how to replace the Q:Q with a column that matches a certain criteria i.e. find the column on SomeSheets where the cell in row 1 matches the text string "XX".

I've tried swapping the Q:Q out for your INDEX / MATCH function, but it breaks. I've also toyed with COLUMN, VLOOKUP etc., but no dice.

Solvable?