Sumif across Multiple sheets

[beenlosteverywhere]

I am having trouble summing across multiple worksheets.
I have a "Totaliser" sheet which has the relivant search info on it.
eg Cells F5:F100 hold the Criteria for the SUMIF.
on the following sheets is the data which needs to be sumed if that criteria
is met.
The value then goes in the cell range D5100 on the Totaliser sheet.
I have to collate about 80 sheets and the data matching might not be on the
same lines within those sheets but the info is in the same coloums.
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)
this appears to only work on one sheet.

The data required is simply counting the qtys of all codes that are the same.

What am I doing wrong?

 

[Pete_UK]

SUMIF doesn't work very well for multiple sheets. What you can do is
to put the SUMIF formulae in each sheet, and then you can sum these on
your Totaliser sheet. For example, say you were to use an out of the
way area in your other sheets, then you could have a formula like this
(maybe in Z1):

=SUMIF(G$3:U$100,Totaliser!F5,G$3:U$100­)

and then copy this down. (Note, it will be easier to group your
sheets, then you will only have to do that once - remember to ungroup
the sheets when you've finished).

Then in your Totaliser sheet you can have:

=SUM('065Aldis:065test2'!Z1)

and then copy this down.

Hope this helps.

Pete

 

[beenlosteverywhere]

Thanks Pete, The sheets containing the data are allready summed to one cell
but i get an error in the formula on the totaliser page if i use this info
(probably because there is a formula in the cell) So that is why i am using
G3:U100 instead of D3100 on the Data sheets.
There is only one of each type of code per sheet but they are in different
locations with each data sheet.