Summing across multiple worksheets in excel 2007
 

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,'0 65Aldis:065test2'!G3:U100)

I have also tried to substitute G3:U100 with D3:D100 (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?

Summing across multiple worksheets in excel 2007
 

SUMIF cannot be used over 3-D ranges.

For one alternative, see

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



In article ,
beenlosteverywhere
wrote:

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,'0 65Aldis:065test2'!G3:U100)

I have also tried to substitute G3:U100 with D3:D100 (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?

Summing across multiple worksheets in excel 2007
 

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))))

--
Regards
Roger Govier

"beenlosteverywhere" wrote in
message ...
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,'0 65Aldis:065test2'!G3:U100)

I have also tried to substitute G3:U100 with D3:D100 (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?

Summing across multiple worksheets in excel 2007
 

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