Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif Across multiple worksheets
I work as a consultant to school districts and in doing so I set up
enrollment projection models in excel workbooks. I am in the process of refining our models and reducing the work we do. My current workbook consists of about 350 worksheets of data (they normally are not this huge). I am trying to set up a summary sheet of all worksheets using a SUMIF command. I want to sum enrollments in cell E93 based on the school name entered in cell S16. My worksheets start with SAZ 1110823:SAZ 5172626 (based on our label system). I tired this command: =SUMIF('SAZ 1110823:SAZ 5172626'!S16,"Agua Caliente",'SAZ 1110823:SAZ 5172626'!E93) And it came back with an error of "A value used in the formula is of the wrong data type". I'm not sure what step to take. I thought this would be a pretty simple formula, but have been stuck for several hours, and haven't really found a solution searching around the message board. Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif Across multiple worksheets
Untested...
If you download and install the free add-in Morefunc.xll, you can use the following formula... =SUMPRODUCT(--(THREED('SAZ 1110823:SAZ 5172626'!S16)="Agua Caliente"),THREED('SAZ 1110823:SAZ 5172626'!E93)) The add-in can be found in the following link... http://xcell05.free.fr/english/index.html Hope this helps! In article , Giantrobot wrote: I work as a consultant to school districts and in doing so I set up enrollment projection models in excel workbooks. I am in the process of refining our models and reducing the work we do. My current workbook consists of about 350 worksheets of data (they normally are not this huge). I am trying to set up a summary sheet of all worksheets using a SUMIF command. I want to sum enrollments in cell E93 based on the school name entered in cell S16. My worksheets start with SAZ 1110823:SAZ 5172626 (based on our label system). I tired this command: =SUMIF('SAZ 1110823:SAZ 5172626'!S16,"Agua Caliente",'SAZ 1110823:SAZ 5172626'!E93) And it came back with an error of "A value used in the formula is of the wrong data type". I'm not sure what step to take. I thought this would be a pretty simple formula, but have been stuck for several hours, and haven't really found a solution searching around the message board. Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif Across multiple worksheets
Worked beautifully!
Thanks for your assistance. "Domenic" wrote: Untested... If you download and install the free add-in Morefunc.xll, you can use the following formula... =SUMPRODUCT(--(THREED('SAZ 1110823:SAZ 5172626'!S16)="Agua Caliente"),THREED('SAZ 1110823:SAZ 5172626'!E93)) The add-in can be found in the following link... http://xcell05.free.fr/english/index.html Hope this helps! In article , Giantrobot wrote: I work as a consultant to school districts and in doing so I set up enrollment projection models in excel workbooks. I am in the process of refining our models and reducing the work we do. My current workbook consists of about 350 worksheets of data (they normally are not this huge). I am trying to set up a summary sheet of all worksheets using a SUMIF command. I want to sum enrollments in cell E93 based on the school name entered in cell S16. My worksheets start with SAZ 1110823:SAZ 5172626 (based on our label system). I tired this command: =SUMIF('SAZ 1110823:SAZ 5172626'!S16,"Agua Caliente",'SAZ 1110823:SAZ 5172626'!E93) And it came back with an error of "A value used in the formula is of the wrong data type". I'm not sure what step to take. I thought this would be a pretty simple formula, but have been stuck for several hours, and haven't really found a solution searching around the message board. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Functions across multiple worksheets | Excel Worksheet Functions | |||
Sumif with multiple worksheets | Excel Worksheet Functions | |||
Update multiple worksheets | Excel Discussion (Misc queries) | |||
how do I arrange multiple worksheets from the same workbook | Excel Discussion (Misc queries) | |||
Extracting data from multiple worksheets into a list | Excel Worksheet Functions |