So, what are we looking for in sheet1:sheet100!a1?

This formula will count the number of times sheet1:sheet3!a1 50 and
sheet1:sheet3!b1 = Bob:

=SUMPRODUCT(--(N(INDIRECT("'Sheet"&{1,2,3}&"'!A1"))50),--(T(INDIRECT("'Sheet"&{1,2,3}&"'!B1"))="Bob"))

Are your sheet names really Sheet1, Sheet2, Sheet3 etc ? If they are and you
really have 100 sheets:

=SUMPRODUCT(--(N(INDIRECT("'Sheet"&ROW(INDIRECT("1:100"))&"'!A1" ))50),--(T(INDIRECT("'Sheet"&ROW(INDIRECT("1:100"))&"'!B1" ))="Bob"))

Or, you can list your sheet names in a range of cells:

H1:H100 = Sheet1...Sheet100

=SUMPRODUCT(--(N(INDIRECT("'"&H1:H100&"'!A1"))50),--(T(INDIRECT("'"&H1:H100&"'!B1"))="Bob"))

Or, you can list the sheet names and give that range a name:

H1:H100 = Sheet1...Sheet100 = List

=SUMPRODUCT(--(N(INDIRECT("'"&List&"'!A1"))50),--(T(INDIRECT("'"&List&"'!B1"))="Bob"))

Or, you can put a much simpler formula on each sheet and sum those cells:

C1 of each sheet: =(A150)*(B1="Bob")

=SUM('Sheet1:Sheet100'!C1)


--
Biff
Microsoft Excel MVP


"wx4usa" wrote in message
oups.com...
Can I define a named range that looks thru worksheets in a workbook
such as sales = sheet1:sheet100!a1? and name = sheet1:sheet100!,b1 and
use these ranges in a sumproduct formula?

I would like a sumproduct formula to look at a certain cell in all
sheets and then look at another cell in these sheets for a particular
name.

Will this work?