On Oct 7, 4:50 pm, "T. Valko" wrote:
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?

Hi Biff,

I have cell a1 is january sales then each sheet is a different
category. Cell b resides on each sheet indicating the category. Then
cell a2 ion each sheet is february, a3 is march and so forth.

I would like to look thru the sheets and sum january, then february,
then march etc sales in category 1 thru 100 all on one report?

Is what Im looking to do possible?