Formulas Across Multiple Worksheets

[ultra429]

I have two array formulas that on one total page pull from only one worksheet. I have another that can use the same essential formulas, but must pull across several worksheets.

Here is the first, where bolded is where I need multiple worksheets:

=SUM(('Orange Co-10'!$E$2:$E$200="John Smith")*('Orange Co-10'!$G$2:$G$200="Sales Incomplete"))

and

=SUMPRODUCT(--('Orange Co-10'!$E$2:$E$200="John Smith"),--('Orange Co-10'!$L$2:$L$200 < 4))

I need both of these to pull across 5-6 worksheets. I figured out how to do it by searching Google with one criteria and with the indirect/direct setup:

(ex:
=SUMPRODUCT(COUNTIF(INDIRECT("'"& {"Centric-E7","East Bay-E7","Sacramento-E7","San Francisco-E7","Santa Clara-E7","Other-E7"} &"'!E2:E200"),"Ashley Miller"))

But I am not well-versed enough to be able to figure out the code on my own.

Any help is appreciated.

 

[Domenic]

If you're using Excel 2007 or later, try...

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&{"Centric-E7","East Bay-E7","Sacramento-E7","San Francisco-E7","Santa Clara-E7","Other-E7"}&"'!E2:E200"),"John Smith",INDIRECT("'"&{"Centric-E7","East Bay-E7","Sacramento-E7","San Francisco-E7","Santa Clara-E7","Other-E7"}&"'!G2:G200"),"Sales Incomplete"))