|
Sumproduct question
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? |
Sumproduct question
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? |
Sumproduct question
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? |
Sumproduct question
"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 |
Sumproduct question
"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 |
Sumproduct question
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 |
| All times are GMT +1. The time now is 01:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com