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?

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?

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?

"wx4usa" wrote in message
ups.com...
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?


I'm not following you on this. If cell A1 on each sheet is January sales and
you want to sum cell A1 on all sheets then:

=SUM(Sheet1:Sheet100!A1)

What does this mean and what does it have to do with it?

Cell b resides on each sheet indicating the category.

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message
...
"wx4usa" wrote in message
ups.com...
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?


I'm not following you on this. If cell A1 on each sheet is January sales
and you want to sum cell A1 on all sheets then:

=SUM(Sheet1:Sheet100!A1)

What does this mean and what does it have to do with it?

Cell b resides on each sheet indicating the category.


Ok, I think I've figured out what you're asking....

Sum A1 on all sheets where B1 = some category.

Try something like this:

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

Where "x" = category

--
Biff
Microsoft Excel MVP

Or this

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&{1,2,3}&"'!B1" ),"x",INDIRECT("'Sheet"&{1,2,3}&"'!A1")))

since there is only one criteria range


--


Regards,


Peo Sjoblom


Ok, I think I've figured out what you're asking....

Sum A1 on all sheets where B1 = some category.

Try something like this:

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

Where "x" = category

--
Biff
Microsoft Excel MVP