Sumproduct question

  • Thread starter Thread starter wx4usa
  • Start date Start date
W

wx4usa

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: =(A1>50)*(B1="Bob")

=SUM('Sheet1:Sheet100'!C1)
 
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: =(A1>50)*(B1="Bob")

=SUM('Sheet1:Sheet100'!C1)
Click to expand...

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 said:
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?
Click to expand...

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?
 
T. Valko said:
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?
Click to expand...

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(INDIRECT("'Sheet"&{1,2,3}&"'!A1")))

Where "x" = category
 
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
 
Back
Top