| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum For Multiple Sheets
I have a (2007) workbook with around 25 Sheets in the following order:
Sheet1 named "Main" Sheet2-Sheet25 named "Branch 1" to "Branch 24" All the branch sheets are in the same structure with column B representing the Product like "Salt", "Sugar" etc., column C representing the Product size like "1/2 Kilo", "1 Kilo" etc. and finally the column D representing the Amount of Sales. Now I have the Covering Sheet in the following manner: 1/2 Kilo 1 Kilo 2 Kilo ... ... ... Salt Sugar .... .... .... What sort of formula should be placed in the B2:Last so as to sum the relevant data from ALL the sheets? Something like =SUMPRODUCT(--('Branch 1':'Branch 24'!B:B="Salt")*--('Branch 1':'Branch 24'!C:C="1/2 Kilo"),'Branch 1':'Branch 24'!D:D) -- Best Regards, FARAZ A. QURESHI |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Sum For Multiple Sheets
Hi,
SUMPRODUCT won't work against 3D range references such as Sheet1:Sheet5!A1 so your best choice would be a custom function or a relative long spreadsheet formula. -- If this helps, please click the Yes button Cheers, Shane Devenshire "FARAZ QURESHI" wrote: I have a (2007) workbook with around 25 Sheets in the following order: Sheet1 named "Main" Sheet2-Sheet25 named "Branch 1" to "Branch 24" All the branch sheets are in the same structure with column B representing the Product like "Salt", "Sugar" etc., column C representing the Product size like "1/2 Kilo", "1 Kilo" etc. and finally the column D representing the Amount of Sales. Now I have the Covering Sheet in the following manner: 1/2 Kilo 1 Kilo 2 Kilo ... ... ... Salt Sugar ... ... ... What sort of formula should be placed in the B2:Last so as to sum the relevant data from ALL the sheets? Something like =SUMPRODUCT(--('Branch 1':'Branch 24'!B:B="Salt")*--('Branch 1':'Branch 24'!C:C="1/2 Kilo"),'Branch 1':'Branch 24'!D:D) -- Best Regards, FARAZ A. QURESHI |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Sum For Multiple Sheets
For one condition only:
=SUMPRODUCT(SUMIF(INDIRECT("Branch"&ROW(INDIRECT(" 1:24"))&"! B1:B100"),"Salt",INDIRECT("Branch"&ROW(INDIRECT("1 :24"))&"!D1:D100"))) JB http://boisgontierjacques.free.fr On 24 jan, 09:50, Shane Devenshire wrote: Hi, SUMPRODUCT won't work against 3D range references such as Sheet1:Sheet5!A1 so your best choice would be a custom function or a relative long spreadsheet formula. * -- If this helps, please click the Yes button Cheers, Shane Devenshire "FARAZ QURESHI" wrote: I have a (2007) workbook with around 25 Sheets in the following order: Sheet1 named "Main" Sheet2-Sheet25 named "Branch 1" to "Branch 24" All the branch sheets are in the same structure with column B representing the Product like "Salt", "Sugar" etc., column C representing the Product size like "1/2 Kilo", "1 Kilo" etc. and finally the column D representing the Amount of Sales. Now I have the Covering Sheet in the following manner: * * * * * 1/2 Kilo * * * * *1 Kilo * * * * *2 Kilo ... ... ... Salt Sugar ... ... ... What sort of formula should be placed in the B2:Last so as to sum the relevant data from ALL the sheets? Something like =SUMPRODUCT(--('Branch 1':'Branch 24'!B:B="Salt")*--('Branch 1':'Branch 24'!C:C="1/2 Kilo"),'Branch 1':'Branch 24'!D:D) -- Best Regards, FARAZ A. QURESHI- Masquer le texte des messages précédents - - Afficher le texte des messages précédents - |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Sum For Multiple Sheets
Whether this is practical or not is for the user to decide!
For multiple SUMPRODUCT conditions across multiple sheets... All Branch n sheets are structured as: B2:B10 = product (text) C2:C10 = size (text) D2:D10 = numbers to sum (numeric) Summary sheet: A2 = some product name B1 = some size Create these named expressions: InsertNameDefne Name: Array1 Refers to: =COLUMN(INDIRECT("A:X")) This evaluates to an array from 1 to 24 which corresponds to your sheet names: Branch 1 to Branch 24 Name: Array2 Refers to: =ROW(INDIRECT("2:10")) This is used for the OFFSET in defining the range address Then this formula (all on one line) to sum D2:D10 across all the sheets where B2:B10=A2 and C2:C10=B1: Breaking it into "chunks" so that line wrap doesn't take out the space characters after each instance of Branch. =SUMPRODUCT(--(T(OFFSET(INDIRECT("'BRANCH " &Array1&"'!B2:B10"),Array2-2,,))=A2), --(T(OFFSET(INDIRECT("'Branch "&Array1&"'!C2:C10") ,Array2-2,,))=B1),N(OFFSET(INDIRECT("'Branch " &Array1&"'!D2:D10"),Array2-2,,))) -- Biff Microsoft Excel MVP "JB" wrote in message ... For one condition only: =SUMPRODUCT(SUMIF(INDIRECT("Branch"&ROW(INDIRECT(" 1:24"))&"! B1:B100"),"Salt",INDIRECT("Branch"&ROW(INDIRECT("1 :24"))&"!D1:D100"))) JB http://boisgontierjacques.free.fr On 24 jan, 09:50, Shane Devenshire wrote: Hi, SUMPRODUCT won't work against 3D range references such as Sheet1:Sheet5!A1 so your best choice would be a custom function or a relative long spreadsheet formula. -- If this helps, please click the Yes button Cheers, Shane Devenshire "FARAZ QURESHI" wrote: I have a (2007) workbook with around 25 Sheets in the following order: Sheet1 named "Main" Sheet2-Sheet25 named "Branch 1" to "Branch 24" All the branch sheets are in the same structure with column B representing the Product like "Salt", "Sugar" etc., column C representing the Product size like "1/2 Kilo", "1 Kilo" etc. and finally the column D representing the Amount of Sales. Now I have the Covering Sheet in the following manner: 1/2 Kilo 1 Kilo 2 Kilo ... ... ... Salt Sugar ... ... ... What sort of formula should be placed in the B2:Last so as to sum the relevant data from ALL the sheets? Something like =SUMPRODUCT(--('Branch 1':'Branch 24'!B:B="Salt")*--('Branch 1':'Branch 24'!C:C="1/2 Kilo"),'Branch 1':'Branch 24'!D:D) -- Best Regards, FARAZ A. QURESHI- Masquer le texte des messages précédents - - Afficher le texte des messages précédents - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Sum For Multiple Sheets
WOW!!!!
Biff XCLENT!!!! By the way... 1. Why doesn't the normal text, instead of name, work? & 2. What if the size is in numerical terms instead of text? THANK YOU VERY VERY MUCH! -- Best Regards, FARAZ A. QURESHI "T. Valko" wrote: Whether this is practical or not is for the user to decide! For multiple SUMPRODUCT conditions across multiple sheets... All Branch n sheets are structured as: B2:B10 = product (text) C2:C10 = size (text) D2:D10 = numbers to sum (numeric) Summary sheet: A2 = some product name B1 = some size Create these named expressions: InsertNameDefne Name: Array1 Refers to: =COLUMN(INDIRECT("A:X")) This evaluates to an array from 1 to 24 which corresponds to your sheet names: Branch 1 to Branch 24 Name: Array2 Refers to: =ROW(INDIRECT("2:10")) This is used for the OFFSET in defining the range address Then this formula (all on one line) to sum D2:D10 across all the sheets where B2:B10=A2 and C2:C10=B1: Breaking it into "chunks" so that line wrap doesn't take out the space characters after each instance of Branch. =SUMPRODUCT(--(T(OFFSET(INDIRECT("'BRANCH " &Array1&"'!B2:B10"),Array2-2,,))=A2), --(T(OFFSET(INDIRECT("'Branch "&Array1&"'!C2:C10") ,Array2-2,,))=B1),N(OFFSET(INDIRECT("'Branch " &Array1&"'!D2:D10"),Array2-2,,))) -- Biff Microsoft Excel MVP "JB" wrote in message ... For one condition only: =SUMPRODUCT(SUMIF(INDIRECT("Branch"&ROW(INDIRECT(" 1:24"))&"! B1:B100"),"Salt",INDIRECT("Branch"&ROW(INDIRECT("1 :24"))&"!D1:D100"))) JB http://boisgontierjacques.free.fr On 24 jan, 09:50, Shane Devenshire wrote: Hi, SUMPRODUCT won't work against 3D range references such as Sheet1:Sheet5!A1 so your best choice would be a custom function or a relative long spreadsheet formula. -- If this helps, please click the Yes button Cheers, Shane Devenshire "FARAZ QURESHI" wrote: I have a (2007) workbook with around 25 Sheets in the following order: Sheet1 named "Main" Sheet2-Sheet25 named "Branch 1" to "Branch 24" All the branch sheets are in the same structure with column B representing the Product like "Salt", "Sugar" etc., column C representing the Product size like "1/2 Kilo", "1 Kilo" etc. and finally the column D representing the Amount of Sales. Now I have the Covering Sheet in the following manner: 1/2 Kilo 1 Kilo 2 Kilo ... ... ... Salt Sugar ... ... ... What sort of formula should be placed in the B2:Last so as to sum the relevant data from ALL the sheets? Something like =SUMPRODUCT(--('Branch 1':'Branch 24'!B:B="Salt")*--('Branch 1':'Branch 24'!C:C="1/2 Kilo"),'Branch 1':'Branch 24'!D:D) -- Best Regards, FARAZ A. QURESHI- Masquer le texte des messages précédents - - Afficher le texte des messages précédents - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Sum For Multiple Sheets
1. Why doesn't the normal text, instead of name, work?
Are you referring to Array1 and Array2? I used those names to shorten the overall length of the formula. 2. What if the size is in numerical terms instead of text? Then you'd change the T function for that array to the N function. Whatever data type you're testing for in a particular array determines which of those functions to use: Arrays that are TEXT use the T function Arrays that are NUMERIC use the N function -- Biff Microsoft Excel MVP "FARAZ QURESHI" wrote in message ... WOW!!!! Biff XCLENT!!!! By the way... 1. Why doesn't the normal text, instead of name, work? & 2. What if the size is in numerical terms instead of text? THANK YOU VERY VERY MUCH! -- Best Regards, FARAZ A. QURESHI "T. Valko" wrote: Whether this is practical or not is for the user to decide! For multiple SUMPRODUCT conditions across multiple sheets... All Branch n sheets are structured as: B2:B10 = product (text) C2:C10 = size (text) D2:D10 = numbers to sum (numeric) Summary sheet: A2 = some product name B1 = some size Create these named expressions: InsertNameDefne Name: Array1 Refers to: =COLUMN(INDIRECT("A:X")) This evaluates to an array from 1 to 24 which corresponds to your sheet names: Branch 1 to Branch 24 Name: Array2 Refers to: =ROW(INDIRECT("2:10")) This is used for the OFFSET in defining the range address Then this formula (all on one line) to sum D2:D10 across all the sheets where B2:B10=A2 and C2:C10=B1: Breaking it into "chunks" so that line wrap doesn't take out the space characters after each instance of Branch. =SUMPRODUCT(--(T(OFFSET(INDIRECT("'BRANCH " &Array1&"'!B2:B10"),Array2-2,,))=A2), --(T(OFFSET(INDIRECT("'Branch "&Array1&"'!C2:C10") ,Array2-2,,))=B1),N(OFFSET(INDIRECT("'Branch " &Array1&"'!D2:D10"),Array2-2,,))) -- Biff Microsoft Excel MVP "JB" wrote in message ... For one condition only: =SUMPRODUCT(SUMIF(INDIRECT("Branch"&ROW(INDIRECT(" 1:24"))&"! B1:B100"),"Salt",INDIRECT("Branch"&ROW(INDIRECT("1 :24"))&"!D1:D100"))) JB http://boisgontierjacques.free.fr On 24 jan, 09:50, Shane Devenshire wrote: Hi, SUMPRODUCT won't work against 3D range references such as Sheet1:Sheet5!A1 so your best choice would be a custom function or a relative long spreadsheet formula. -- If this helps, please click the Yes button Cheers, Shane Devenshire "FARAZ QURESHI" wrote: I have a (2007) workbook with around 25 Sheets in the following order: Sheet1 named "Main" Sheet2-Sheet25 named "Branch 1" to "Branch 24" All the branch sheets are in the same structure with column B representing the Product like "Salt", "Sugar" etc., column C representing the Product size like "1/2 Kilo", "1 Kilo" etc. and finally the column D representing the Amount of Sales. Now I have the Covering Sheet in the following manner: 1/2 Kilo 1 Kilo 2 Kilo ... ... ... Salt Sugar ... ... ... What sort of formula should be placed in the B2:Last so as to sum the relevant data from ALL the sheets? Something like =SUMPRODUCT(--('Branch 1':'Branch 24'!B:B="Salt")*--('Branch 1':'Branch 24'!C:C="1/2 Kilo"),'Branch 1':'Branch 24'!D:D) -- Best Regards, FARAZ A. QURESHI- Masquer le texte des messages précédents - - Afficher le texte des messages précédents - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Sum For Multiple Sheets
I am using a very similar function
=SUMPRODUCT(SUMIF((INDIRECT("'"&List&"'!A4:A35")), "Black",INDIRECT("'"&List&"'!B4:B35"))) Where "List" is a specific list of worksheets. However "List" isn't completely filled out, I am continually adding sheets and these are appended to "List." This formula works, except for the cells without any value in "List" excel returns a "REF! error. Is it possible to embed an ISERROR() or IFERROR() into this function? "JB" wrote: For one condition only: =SUMPRODUCT(SUMIF(INDIRECT("Branch"&ROW(INDIRECT(" 1:24"))&"! B1:B100"),"Salt",INDIRECT("Branch"&ROW(INDIRECT("1 :24"))&"!D1:D100"))) JB http://boisgontierjacques.free.fr On 24 jan, 09:50, Shane Devenshire wrote: Hi, SUMPRODUCT won't work against 3D range references such as Sheet1:Sheet5!A1 so your best choice would be a custom function or a relative long spreadsheet formula. -- If this helps, please click the Yes button Cheers, Shane Devenshire "FARAZ QURESHI" wrote: I have a (2007) workbook with around 25 Sheets in the following order: Sheet1 named "Main" Sheet2-Sheet25 named "Branch 1" to "Branch 24" All the branch sheets are in the same structure with column B representing the Product like "Salt", "Sugar" etc., column C representing the Product size like "1/2 Kilo", "1 Kilo" etc. and finally the column D representing the Amount of Sales. Now I have the Covering Sheet in the following manner: 1/2 Kilo 1 Kilo 2 Kilo ... ... ... Salt Sugar ... ... ... What sort of formula should be placed in the B2:Last so as to sum the relevant data from ALL the sheets? Something like =SUMPRODUCT(--('Branch 1':'Branch 24'!B:B="Salt")*--('Branch 1':'Branch 24'!C:C="1/2 Kilo"),'Branch 1':'Branch 24'!D:D) -- Best Regards, FARAZ A. QURESHI- Masquer le texte des messages précédents - - Afficher le texte des messages précédents - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Sum For Multiple Sheets
Use dynamic ranged for List (in column E):
=Offset($E$2,,,CountA($E:$E)-1) JB On 17 fév, 00:31, mitch wrote: I am using a very similar function =SUMPRODUCT(SUMIF((INDIRECT("'"&List&"'!A4:A35")), "Black",INDIRECT("'"&List*&"'!B4:B35"))) Where "List" is a specific list of worksheets. However "List" isn't completely filled out, I am continually adding sheets and these are appended to "List." This formula works, except for the cells without any value in "List" excel returns a "REF! error. Is it possible to embed an ISERROR() or IFERROR() into this function? "JB" wrote: For one condition only: =SUMPRODUCT(SUMIF(INDIRECT("Branch"&ROW(INDIRECT(" 1:24"))&"! B1:B100"),"Salt",INDIRECT("Branch"&ROW(INDIRECT("1 :24"))&"!D1:D100"))) JB http://boisgontierjacques.free.fr On 24 jan, 09:50, Shane Devenshire wrote: Hi, SUMPRODUCT won't work against 3D range references such as Sheet1:Sheet5!A1 so your best choice would be a custom function or a relative long spreadsheet formula. * -- If this helps, please click the Yes button Cheers, Shane Devenshire "FARAZ QURESHI" wrote: I have a (2007) workbook with around 25 Sheets in the following order: Sheet1 named "Main" Sheet2-Sheet25 named "Branch 1" to "Branch 24" All the branch sheets are in the same structure with column B representing the Product like "Salt", "Sugar" etc., column C representing the Product size like "1/2 Kilo", "1 Kilo" etc. and finally the column D representing the Amount of Sales. Now I have the Covering Sheet in the following manner: * * * * * 1/2 Kilo * * * * *1 Kilo * * * * *2 Kilo ... ... ... Salt Sugar ... ... ... What sort of formula should be placed in the B2:Last so as to sum the relevant data from ALL the sheets? Something like =SUMPRODUCT(--('Branch 1':'Branch 24'!B:B="Salt")*--('Branch 1':'Branch 24'!C:C="1/2 Kilo"),'Branch 1':'Branch 24'!D:D) -- Best Regards, FARAZ A. QURESHI- Masquer le texte des messages précédents - - Afficher le texte des messages précédents -- Masquer le texte des messages précédents - - Afficher le texte des messages précédents - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Sum For Multiple Sheets
I figured it out just as you responded...I've never used Dynamic ranges
before but they are fantastic! "JB" wrote: Use dynamic ranged for List (in column E): =Offset($E$2,,,CountA($E:$E)-1) JB On 17 fév, 00:31, mitch wrote: I am using a very similar function =SUMPRODUCT(SUMIF((INDIRECT("'"&List&"'!A4:A35")), "Black",INDIRECT("'"&ListÂ*&"'!B4:B35"))) Where "List" is a specific list of worksheets. However "List" isn't completely filled out, I am continually adding sheets and these are appended to "List." This formula works, except for the cells without any value in "List" excel returns a "REF! error. Is it possible to embed an ISERROR() or IFERROR() into this function? "JB" wrote: For one condition only: =SUMPRODUCT(SUMIF(INDIRECT("Branch"&ROW(INDIRECT(" 1:24"))&"! B1:B100"),"Salt",INDIRECT("Branch"&ROW(INDIRECT("1 :24"))&"!D1:D100"))) JB http://boisgontierjacques.free.fr On 24 jan, 09:50, Shane Devenshire wrote: Hi, SUMPRODUCT won't work against 3D range references such as Sheet1:Sheet5!A1 so your best choice would be a custom function or a relative long spreadsheet formula. -- If this helps, please click the Yes button Cheers, Shane Devenshire "FARAZ QURESHI" wrote: I have a (2007) workbook with around 25 Sheets in the following order: Sheet1 named "Main" Sheet2-Sheet25 named "Branch 1" to "Branch 24" All the branch sheets are in the same structure with column B representing the Product like "Salt", "Sugar" etc., column C representing the Product size like "1/2 Kilo", "1 Kilo" etc. and finally the column D representing the Amount of Sales. Now I have the Covering Sheet in the following manner: 1/2 Kilo 1 Kilo 2 Kilo ... ... ... Salt Sugar ... ... ... What sort of formula should be placed in the B2:Last so as to sum the relevant data from ALL the sheets? Something like =SUMPRODUCT(--('Branch 1':'Branch 24'!B:B="Salt")*--('Branch 1':'Branch 24'!C:C="1/2 Kilo"),'Branch 1':'Branch 24'!D:D) -- Best Regards, FARAZ A. QURESHI- Masquer le texte des messages précédents - - Afficher le texte des messages précédents -- Masquer le texte des messages précédents - - Afficher le texte des messages précédents - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT, SUMIF, COUNTIF for multiple sheets for multiple criter | Excel Worksheet Functions | |||
| Conditional Formatting using multiple sheets | Excel Worksheet Functions | |||
| Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
| Why Not Conditional Formatting For Multiple Sheets? | New Users to Excel | |||
| Conditional Sum and multiple conditions across different sheets | Excel Worksheet Functions | |||
Linear Mode
