Summing across multiple worksheets in excel 2007

[beenlosteverywhere]

I am having trouble summing across multiple worksheets.
I have a "Totaliser" sheet which has the relivant search info on it.
Cells F5:F100 hold the Criteria for the SUMIF.
The sheets, following the Totaliser sheet, contain the data which needs to
be sumed if that criteria is met. (approx 80 sheets).
These sheets are all the same format/layout.
The criteria info is in the same colunm but maybe on a different line.
The data required is simply summing the summed qtys of all codes that are
the same OR alternativly if formula cells cant be summed, then summing of the
entire range that meets the criteria.

This is the formula i have tried to use (on 2 sheets to start)
=SUMIF('065Aldis:065test2'!G3:U100,Totaliser!F5,'065Aldis:065test2'!G3:U100)

I have also tried to substitute G3:U100 with D3100 (Coloumn D contains Sum
G3:U3 filled down)

The first formula appears to only work on one sheet.
The second formual comes back as an error.

Can anybody help?

 

[JE McGimpsey]

SUMIF cannot be used over 3-D ranges.

For one alternative, see

http://www.mcgimpsey.com/excel/threedsumif.html

 

[Roger Govier]

Hi

On your Totaliser sheet, enter the list of Sheet names and ranges in a
series of cells
e.g. A1 065Aldis!G3:U100, A2 065test2!G3:U100 etc.
Give this list of cells a name, say myRange
Then use the formula
=SUMPRODUCT((SUMIF(INDIRECT(myRange),Totaliser!F5,INDIRECT(myRange))))

 

[Herbert Seidenberg]

Same formula as Roger's but in terms of
Excel 2007 Tables.
Crosschecked with PivotTable
http://www.mediafire.com/file/zdmivylm1mw/01_19_09.xlsx