Sumproduct across worksheets

[mikelee101]

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

 

[T. Valko]

What version of Excel are you using?

 

[mikelee101]

Sorry...I usually try to include info like that. Excel03, XP Pro SP2.

Thanks,
Mike

 

[T. Valko]

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


Sorry...I usually try to include info like that. Excel03, XP Pro SP2.

Thanks,
Mike