Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif across Multiple sheets
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 D5:D100 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,'0 65Aldis: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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif across Multiple sheets
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 On Jan 12, 4:58*am, beenlosteverywhere wrote: 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 D5:D100 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,'0 65Aldis: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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif across Multiple sheets
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 D3:D100 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. "Pete_UK" wrote: 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 On Jan 12, 4:58 am, beenlosteverywhere wrote: 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 D5:D100 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,'0 65Aldis: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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif over multiple sheets | Excel Discussion (Misc queries) | |||
SUMPRODUCT, SUMIF, COUNTIF for multiple sheets for multiple criter | Excel Worksheet Functions | |||
SUMIF with multiple sheets | Excel Discussion (Misc queries) | |||
sumif across multiple sheets | Excel Worksheet Functions | |||
SUMIF on multiple sheets | Excel Worksheet Functions |