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

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

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 -

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 -