Sum certain cells in certain columns on multiple worksheets

I'm trying to write a formula in Sheet1 that sums some cells in a particular column on all other worksheets. The particular column on the other worksheets will not always have the same column reference, but the cell in row 1 of that column will always have the text string Hours. The particular cells in the column I want to sum also won't always have the same row references, but I can identify them when an adjacent cell contains the text string Project.

So I'm basically using a combination of the SUMPRODUCT and SUMIF functions.

I've got this formula working fine:

=SUMPRODUCT(SUMIF(INDIRECT("'SomeSheets'!$A:$A"),"Project",INDIRECT("'SomeSheets'!B:B")))

but it only checks the B:B column, which isn't always the column I need.

So I need to replace the B:B reference with a function that checks all the columns between B:Z and only sums the column whose cell in row 1 contains Hours.

I've tried swapping the B:B out for:

&(INDEX('SomeSheets'!B:Z,0,MATCH("Hours",'SomeSheets'!B1:Z1,0))

but it breaks.

I've also toyed with COLUMN and VLOOKUP but no dice.

Any ideas would be much appreciated!

Try

=SUMPRODUCT(SUMIF(INDIRECT("'SomeSheets'!$A1:$A1000"),"Project",OFFSET(INDIRECT("'SomeSheets'!A1"),,MATCH("Hours",INDIRECT("'SomeSheets'!A1:Z1"),0)-1,1000,1)))

Change range to suit your likely maximum

Thanks for your input. I've given it a go, but I'm not getting a good result. The OFFSET function in there is giving me the contents of the cells I want to check, not the cell references.

Thanks for the suggestion John, I've actually managed to fix it. The MATCH command wasn't able to look across multiple worksheets. So I've split the function into separate cells - one works out which column I need to look at on each worksheet, the other does the adding.

Glad it's fixed.