Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct across worksheets
Hello,
I'm trying to do a multi-criteria count across worksheets and was wondering if anyone had any suggestions. The number of sheets may change occassionally, so I'm trying to avoid having to do the calculation over each sheet then sum the results. What I'm trying to accomplish is something like the following: =Sumproduct(--('Sheet1:Sheet9'!$A$1:$A$20="X"),--('Sheet1:Sheet9'!$B $1:$B$20="Y")) This returns #REF!, as does using {=SUM(IF.....}. Does anyone have any ideas on how I can do this with existing functions before I sit down and try to write my own? Any help would be greatly appreciated. Thanks to all. Mike Lee McKinney, TX |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct across worksheets
What version of Excel are you using?
-- Biff Microsoft Excel MVP "mikelee101" wrote in message ... Hello, I'm trying to do a multi-criteria count across worksheets and was wondering if anyone had any suggestions. The number of sheets may change occassionally, so I'm trying to avoid having to do the calculation over each sheet then sum the results. What I'm trying to accomplish is something like the following: =Sumproduct(--('Sheet1:Sheet9'!$A$1:$A$20="X"),--('Sheet1:Sheet9'!$B $1:$B$20="Y")) This returns #REF!, as does using {=SUM(IF.....}. Does anyone have any ideas on how I can do this with existing functions before I sit down and try to write my own? Any help would be greatly appreciated. Thanks to all. Mike Lee McKinney, TX |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct across worksheets
Sorry...I usually try to include info like that. Excel03, XP Pro SP2.
Thanks, Mike On Jan 31, 12:47*pm, "T. Valko" wrote: What version of Excel are you using? -- Biff Microsoft Excel MVP "mikelee101" wrote in message ... Hello, I'm trying to do a multi-criteria count across worksheets and was wondering if anyone had any suggestions. *The number of sheets may change occassionally, so I'm trying to avoid having to do the calculation over each sheet then sum the results. *What I'm trying to accomplish is something like the following: =Sumproduct(--('Sheet1:Sheet9'!$A$1:$A$20="X"),--('Sheet1:Sheet9'!$B $1:$B$20="Y")) This returns #REF!, as does using {=SUM(IF.....}. Does anyone have any ideas on how I can do this with existing functions before I sit down and try to write my own? Any help would be greatly appreciated. Thanks to all. Mike Lee McKinney, TX- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct across worksheets
In Excel 2007 this can be done *relatively* easily but in other versions
it's not at all easy, just the opposite. I would just bite-the-bullet and put a formula on each sheet then sum the results. -- Biff Microsoft Excel MVP "mikelee101" wrote in message ... Sorry...I usually try to include info like that. Excel03, XP Pro SP2. Thanks, Mike On Jan 31, 12:47 pm, "T. Valko" wrote: What version of Excel are you using? -- Biff Microsoft Excel MVP "mikelee101" wrote in message ... Hello, I'm trying to do a multi-criteria count across worksheets and was wondering if anyone had any suggestions. The number of sheets may change occassionally, so I'm trying to avoid having to do the calculation over each sheet then sum the results. What I'm trying to accomplish is something like the following: =Sumproduct(--('Sheet1:Sheet9'!$A$1:$A$20="X"),--('Sheet1:Sheet9'!$B $1:$B$20="Y")) This returns #REF!, as does using {=SUM(IF.....}. Does anyone have any ideas on how I can do this with existing functions before I sit down and try to write my own? Any help would be greatly appreciated. Thanks to all. Mike Lee McKinney, TX- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct multiple values on other worksheets | Excel Worksheet Functions | |||
SUMPRODUCT multiple critera from different worksheets | Excel Worksheet Functions | |||
Sumproduct across multiple worksheets | Excel Worksheet Functions | |||
SUMif or SUMproduct across multiple worksheets? | Excel Worksheet Functions | |||
SUMPRODUCT across worksheets | Excel Discussion (Misc queries) |