Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif
How do I enter more than one worksheet in the range function of the sumif
formula and hence in the sum_range function? -- John S |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif
You can't do it like that. Provide more detail.
-- Biff Microsoft Excel MVP "John S" wrote in message ... How do I enter more than one worksheet in the range function of the sumif formula and hence in the sum_range function? -- John S |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif-gathering data from more than one worksheet
Thanks for responding. I need to gather data from more than one worksheet
when it meets a particular criteria. In principle I'm trying to match a budget coding number from the master spreadsheet, 'the budget', to the corresponding budget codes from the invoices in another spreadsheet with one worksheet corresponding to one invoice. Once matched then I need to sum the corresponding dollars from the invoices onto the budget spreadsheet. -- John S "T. Valko" wrote: You can't do it like that. Provide more detail. -- Biff Microsoft Excel MVP "John S" wrote in message ... How do I enter more than one worksheet in the range function of the sumif formula and hence in the sum_range function? -- John S |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif-gathering data from more than one worksheet
Let's assume you have 5 sheets and you want to sum the range B1:B10 on each
sheet where the range A1:A10 on each sheet contains the entry "X". If your sheet names are all completely unique (North,East,South,West) then you have to list the sheet names in a range of cells. If the sheet names follow some sort of sequential pattern (Week1,Week2,Week3) then we can do it without listing the sheet names. So, I'll show you how to do it with listing the sheet names. Assume L1:L5 = sheet names =SUMPRODUCT(SUMIF(INDIRECT("'"&L1:L5&"'!A1:A10")," X",INDIRECT("'"&L1:L5&"'!B1:B10"))) -- Biff Microsoft Excel MVP "John S" wrote in message ... Thanks for responding. I need to gather data from more than one worksheet when it meets a particular criteria. In principle I'm trying to match a budget coding number from the master spreadsheet, 'the budget', to the corresponding budget codes from the invoices in another spreadsheet with one worksheet corresponding to one invoice. Once matched then I need to sum the corresponding dollars from the invoices onto the budget spreadsheet. -- John S "T. Valko" wrote: You can't do it like that. Provide more detail. -- Biff Microsoft Excel MVP "John S" wrote in message ... How do I enter more than one worksheet in the range function of the sumif formula and hence in the sum_range function? -- John S |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif-gathering data from more than one worksheet
Thanks so much for your help. It would probably help if you knew that I'm
very inexperienced in what we are discussing. After digesting what you said I entered this formula: =SUMPRODUCT(SUMIF(INDIRECT(""&L1:L5&"!A12:A23""),A 31,INDIRECT(''"&L1:L5&"!K12:K23""))) Excel responded in various manners when I got to various parts. I barely register what you are explaining so here are my questions. 1. Before assuming L1:L5=Sheet Names do I have to list all the names of the sheets in those cells? And then do I have to define/name that range reference? 2: How does excel recognize that &L1:L5& refers to a different spreadsheet than the one in which I'm working? 3. Is the A: reference for the range in SumIf, and A:31 the criteria and K: the sum_range? The following formula is the first one that I came up with that would only sum for one worksheet. Maybe this will help you see what I was attempting. SUMIF('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23,A29,'[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) When entering this formula I was in a different excel spreadsheet. I got stuck when I attempted to enter a second sheet from Skillman Invoices into the mix for range and sum_range. Any help you could offer would be much appreciated. Thanks for your help so far. -- John S "T. Valko" wrote: Let's assume you have 5 sheets and you want to sum the range B1:B10 on each sheet where the range A1:A10 on each sheet contains the entry "X". If your sheet names are all completely unique (North,East,South,West) then you have to list the sheet names in a range of cells. If the sheet names follow some sort of sequential pattern (Week1,Week2,Week3) then we can do it without listing the sheet names. So, I'll show you how to do it with listing the sheet names. Assume L1:L5 = sheet names =SUMPRODUCT(SUMIF(INDIRECT("'"&L1:L5&"'!A1:A10")," X",INDIRECT("'"&L1:L5&"'!B1:B10"))) -- Biff Microsoft Excel MVP "John S" wrote in message ... Thanks for responding. I need to gather data from more than one worksheet when it meets a particular criteria. In principle I'm trying to match a budget coding number from the master spreadsheet, 'the budget', to the corresponding budget codes from the invoices in another spreadsheet with one worksheet corresponding to one invoice. Once matched then I need to sum the corresponding dollars from the invoices onto the budget spreadsheet. -- John S "T. Valko" wrote: You can't do it like that. Provide more detail. -- Biff Microsoft Excel MVP "John S" wrote in message ... How do I enter more than one worksheet in the range function of the sumif formula and hence in the sum_range function? -- John S |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif-gathering data from more than one worksheet
Ok, it looks like you want to reference another file. This changes things
considerably! There is no way to do this with a single formula. You'll have to use a separate formula for each sheet that you want to reference. Also, the SUMIF function doesn't work when the referenced file is closed. So, based on your posted sample formula: SUMIF('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23,A29, '[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) Try this: =SUMPRODUCT(--('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23=A29),'[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) You'll have to use a formula for each sheet then you can get the grand total from the subtotals. Here's an "easy" way to do this... Have the source file open (Skillman Invoices.xls) In the file where you want the formulas, start typing the formula: =SUMPRODUCT(--( When you get to that point use your mouse and navigate to the source file and source sheet and point to the ranges. Excel will automatically add the source path for you: =SUMPRODUCT(--('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23 Then finish by typing in the rest of the that part of the formula: = A29), Then repeat the mouse pointing process for the other range: '[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) -- Biff Microsoft Excel MVP "John S" wrote in message ... Thanks so much for your help. It would probably help if you knew that I'm very inexperienced in what we are discussing. After digesting what you said I entered this formula: =SUMPRODUCT(SUMIF(INDIRECT(""&L1:L5&"!A12:A23""),A 31,INDIRECT(''"&L1:L5&"!K12:K23""))) Excel responded in various manners when I got to various parts. I barely register what you are explaining so here are my questions. 1. Before assuming L1:L5=Sheet Names do I have to list all the names of the sheets in those cells? And then do I have to define/name that range reference? 2: How does excel recognize that &L1:L5& refers to a different spreadsheet than the one in which I'm working? 3. Is the A: reference for the range in SumIf, and A:31 the criteria and K: the sum_range? The following formula is the first one that I came up with that would only sum for one worksheet. Maybe this will help you see what I was attempting. SUMIF('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23,A29,'[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) When entering this formula I was in a different excel spreadsheet. I got stuck when I attempted to enter a second sheet from Skillman Invoices into the mix for range and sum_range. Any help you could offer would be much appreciated. Thanks for your help so far. -- John S "T. Valko" wrote: Let's assume you have 5 sheets and you want to sum the range B1:B10 on each sheet where the range A1:A10 on each sheet contains the entry "X". If your sheet names are all completely unique (North,East,South,West) then you have to list the sheet names in a range of cells. If the sheet names follow some sort of sequential pattern (Week1,Week2,Week3) then we can do it without listing the sheet names. So, I'll show you how to do it with listing the sheet names. Assume L1:L5 = sheet names =SUMPRODUCT(SUMIF(INDIRECT("'"&L1:L5&"'!A1:A10")," X",INDIRECT("'"&L1:L5&"'!B1:B10"))) -- Biff Microsoft Excel MVP "John S" wrote in message ... Thanks for responding. I need to gather data from more than one worksheet when it meets a particular criteria. In principle I'm trying to match a budget coding number from the master spreadsheet, 'the budget', to the corresponding budget codes from the invoices in another spreadsheet with one worksheet corresponding to one invoice. Once matched then I need to sum the corresponding dollars from the invoices onto the budget spreadsheet. -- John S "T. Valko" wrote: You can't do it like that. Provide more detail. -- Biff Microsoft Excel MVP "John S" wrote in message ... How do I enter more than one worksheet in the range function of the sumif formula and hence in the sum_range function? -- John S |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif-gathering data from more than one worksheet
I can't express how grateful I am for your time and help. I went ahead and
moved the excel sheet that was in a different workbook into the same workbook that the invoices are in, so now in the same workbook I have the budget worksheet and all the invoice worksheets. Based on your reply I entered this formula in the appropriate spot on the budget worksheet: SUMPRODUCT(--('Invoice 1'!A15:A26=A32),('Invoice 1'!K15:K26)) It did work but just for the specified invoice, #1. So thank you. How do I gather the same data for A:32 from the other invoices in different worksheets? -- John S "T. Valko" wrote: Ok, it looks like you want to reference another file. This changes things considerably! There is no way to do this with a single formula. You'll have to use a separate formula for each sheet that you want to reference. Also, the SUMIF function doesn't work when the referenced file is closed. So, based on your posted sample formula: SUMIF('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23,A29, '[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) Try this: =SUMPRODUCT(--('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23=A29),'[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) You'll have to use a formula for each sheet then you can get the grand total from the subtotals. Here's an "easy" way to do this... Have the source file open (Skillman Invoices.xls) In the file where you want the formulas, start typing the formula: =SUMPRODUCT(--( When you get to that point use your mouse and navigate to the source file and source sheet and point to the ranges. Excel will automatically add the source path for you: =SUMPRODUCT(--('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23 Then finish by typing in the rest of the that part of the formula: = A29), Then repeat the mouse pointing process for the other range: '[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) -- Biff Microsoft Excel MVP "John S" wrote in message ... Thanks so much for your help. It would probably help if you knew that I'm very inexperienced in what we are discussing. After digesting what you said I entered this formula: =SUMPRODUCT(SUMIF(INDIRECT(""&L1:L5&"!A12:A23""),A 31,INDIRECT(''"&L1:L5&"!K12:K23""))) Excel responded in various manners when I got to various parts. I barely register what you are explaining so here are my questions. 1. Before assuming L1:L5=Sheet Names do I have to list all the names of the sheets in those cells? And then do I have to define/name that range reference? 2: How does excel recognize that &L1:L5& refers to a different spreadsheet than the one in which I'm working? 3. Is the A: reference for the range in SumIf, and A:31 the criteria and K: the sum_range? The following formula is the first one that I came up with that would only sum for one worksheet. Maybe this will help you see what I was attempting. SUMIF('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23,A29,'[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) When entering this formula I was in a different excel spreadsheet. I got stuck when I attempted to enter a second sheet from Skillman Invoices into the mix for range and sum_range. Any help you could offer would be much appreciated. Thanks for your help so far. -- John S "T. Valko" wrote: Let's assume you have 5 sheets and you want to sum the range B1:B10 on each sheet where the range A1:A10 on each sheet contains the entry "X". If your sheet names are all completely unique (North,East,South,West) then you have to list the sheet names in a range of cells. If the sheet names follow some sort of sequential pattern (Week1,Week2,Week3) then we can do it without listing the sheet names. So, I'll show you how to do it with listing the sheet names. Assume L1:L5 = sheet names =SUMPRODUCT(SUMIF(INDIRECT("'"&L1:L5&"'!A1:A10")," X",INDIRECT("'"&L1:L5&"'!B1:B10"))) -- Biff Microsoft Excel MVP "John S" wrote in message ... Thanks for responding. I need to gather data from more than one worksheet when it meets a particular criteria. In principle I'm trying to match a budget coding number from the master spreadsheet, 'the budget', to the corresponding budget codes from the invoices in another spreadsheet with one worksheet corresponding to one invoice. Once matched then I need to sum the corresponding dollars from the invoices onto the budget spreadsheet. -- John S "T. Valko" wrote: You can't do it like that. Provide more detail. -- Biff Microsoft Excel MVP "John S" wrote in message ... How do I enter more than one worksheet in the range function of the sumif formula and hence in the sum_range function? -- John S |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif-gathering data from more than one worksheet
Ok, with all the sheets in the same file...
All sheets following the sequential name pattern: Invoice 1, Invoice 2, Invoice 3, etc. All on one line: =SUMPRODUCT(SUMIF(INDIRECT("'Invoice "& ROW(INDIRECT("1:n"))&"'!A15:A26"),A32,INDIRECT ("'Invoice "&ROW(INDIRECT("1:n"))&"'!K15:K26"))) Where n = the number of the last sheet in the sequence. If you want to do the SUMIF on Invoice 1, Invoice 2, Invoice 3, then n = 3. Note that every sheet referenced must exist. If you have 3 sheets and set n to 5 to allow for future sheets to be added then that would cause an error. Some parts of the formula are kind of cryptic. All those quotes!!! Before Invoice and before ! is a double quote followed by a single quote: " ' Invoice " ' ! At first glance they may look like 3 single quotes: ''', but they're not! -- Biff Microsoft Excel MVP "John S" wrote in message ... I can't express how grateful I am for your time and help. I went ahead and moved the excel sheet that was in a different workbook into the same workbook that the invoices are in, so now in the same workbook I have the budget worksheet and all the invoice worksheets. Based on your reply I entered this formula in the appropriate spot on the budget worksheet: SUMPRODUCT(--('Invoice 1'!A15:A26=A32),('Invoice 1'!K15:K26)) It did work but just for the specified invoice, #1. So thank you. How do I gather the same data for A:32 from the other invoices in different worksheets? -- John S "T. Valko" wrote: Ok, it looks like you want to reference another file. This changes things considerably! There is no way to do this with a single formula. You'll have to use a separate formula for each sheet that you want to reference. Also, the SUMIF function doesn't work when the referenced file is closed. So, based on your posted sample formula: SUMIF('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23,A29, '[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) Try this: =SUMPRODUCT(--('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23=A29),'[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) You'll have to use a formula for each sheet then you can get the grand total from the subtotals. Here's an "easy" way to do this... Have the source file open (Skillman Invoices.xls) In the file where you want the formulas, start typing the formula: =SUMPRODUCT(--( When you get to that point use your mouse and navigate to the source file and source sheet and point to the ranges. Excel will automatically add the source path for you: =SUMPRODUCT(--('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23 Then finish by typing in the rest of the that part of the formula: = A29), Then repeat the mouse pointing process for the other range: '[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) -- Biff Microsoft Excel MVP "John S" wrote in message ... Thanks so much for your help. It would probably help if you knew that I'm very inexperienced in what we are discussing. After digesting what you said I entered this formula: =SUMPRODUCT(SUMIF(INDIRECT(""&L1:L5&"!A12:A23""),A 31,INDIRECT(''"&L1:L5&"!K12:K23""))) Excel responded in various manners when I got to various parts. I barely register what you are explaining so here are my questions. 1. Before assuming L1:L5=Sheet Names do I have to list all the names of the sheets in those cells? And then do I have to define/name that range reference? 2: How does excel recognize that &L1:L5& refers to a different spreadsheet than the one in which I'm working? 3. Is the A: reference for the range in SumIf, and A:31 the criteria and K: the sum_range? The following formula is the first one that I came up with that would only sum for one worksheet. Maybe this will help you see what I was attempting. SUMIF('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23,A29,'[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) When entering this formula I was in a different excel spreadsheet. I got stuck when I attempted to enter a second sheet from Skillman Invoices into the mix for range and sum_range. Any help you could offer would be much appreciated. Thanks for your help so far. -- John S "T. Valko" wrote: Let's assume you have 5 sheets and you want to sum the range B1:B10 on each sheet where the range A1:A10 on each sheet contains the entry "X". If your sheet names are all completely unique (North,East,South,West) then you have to list the sheet names in a range of cells. If the sheet names follow some sort of sequential pattern (Week1,Week2,Week3) then we can do it without listing the sheet names. So, I'll show you how to do it with listing the sheet names. Assume L1:L5 = sheet names =SUMPRODUCT(SUMIF(INDIRECT("'"&L1:L5&"'!A1:A10")," X",INDIRECT("'"&L1:L5&"'!B1:B10"))) -- Biff Microsoft Excel MVP "John S" wrote in message ... Thanks for responding. I need to gather data from more than one worksheet when it meets a particular criteria. In principle I'm trying to match a budget coding number from the master spreadsheet, 'the budget', to the corresponding budget codes from the invoices in another spreadsheet with one worksheet corresponding to one invoice. Once matched then I need to sum the corresponding dollars from the invoices onto the budget spreadsheet. -- John S "T. Valko" wrote: You can't do it like that. Provide more detail. -- Biff Microsoft Excel MVP "John S" wrote in message ... How do I enter more than one worksheet in the range function of the sumif formula and hence in the sum_range function? -- John S |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif-gathering data from more than one worksheet
'Your the Man!' I have entered the formula in my experimental workbook with
a functioning budget and two sample invoices and it worked! It summed for the budget coding number from both invoices. There is no way I could have ever come up with that on my own. I don't know how I can repay you except with a hearty thanks. "Thanks" -- John S "T. Valko" wrote: Ok, with all the sheets in the same file... All sheets following the sequential name pattern: Invoice 1, Invoice 2, Invoice 3, etc. All on one line: =SUMPRODUCT(SUMIF(INDIRECT("'Invoice "& ROW(INDIRECT("1:n"))&"'!A15:A26"),A32,INDIRECT ("'Invoice "&ROW(INDIRECT("1:n"))&"'!K15:K26"))) Where n = the number of the last sheet in the sequence. If you want to do the SUMIF on Invoice 1, Invoice 2, Invoice 3, then n = 3. Note that every sheet referenced must exist. If you have 3 sheets and set n to 5 to allow for future sheets to be added then that would cause an error. Some parts of the formula are kind of cryptic. All those quotes!!! Before Invoice and before ! is a double quote followed by a single quote: " ' Invoice " ' ! At first glance they may look like 3 single quotes: ''', but they're not! -- Biff Microsoft Excel MVP "John S" wrote in message ... I can't express how grateful I am for your time and help. I went ahead and moved the excel sheet that was in a different workbook into the same workbook that the invoices are in, so now in the same workbook I have the budget worksheet and all the invoice worksheets. Based on your reply I entered this formula in the appropriate spot on the budget worksheet: SUMPRODUCT(--('Invoice 1'!A15:A26=A32),('Invoice 1'!K15:K26)) It did work but just for the specified invoice, #1. So thank you. How do I gather the same data for A:32 from the other invoices in different worksheets? -- John S "T. Valko" wrote: Ok, it looks like you want to reference another file. This changes things considerably! There is no way to do this with a single formula. You'll have to use a separate formula for each sheet that you want to reference. Also, the SUMIF function doesn't work when the referenced file is closed. So, based on your posted sample formula: SUMIF('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23,A29, '[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) Try this: =SUMPRODUCT(--('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23=A29),'[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) You'll have to use a formula for each sheet then you can get the grand total from the subtotals. Here's an "easy" way to do this... Have the source file open (Skillman Invoices.xls) In the file where you want the formulas, start typing the formula: =SUMPRODUCT(--( When you get to that point use your mouse and navigate to the source file and source sheet and point to the ranges. Excel will automatically add the source path for you: =SUMPRODUCT(--('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23 Then finish by typing in the rest of the that part of the formula: = A29), Then repeat the mouse pointing process for the other range: '[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) -- Biff Microsoft Excel MVP "John S" wrote in message ... Thanks so much for your help. It would probably help if you knew that I'm very inexperienced in what we are discussing. After digesting what you said I entered this formula: =SUMPRODUCT(SUMIF(INDIRECT(""&L1:L5&"!A12:A23""),A 31,INDIRECT(''"&L1:L5&"!K12:K23""))) Excel responded in various manners when I got to various parts. I barely register what you are explaining so here are my questions. 1. Before assuming L1:L5=Sheet Names do I have to list all the names of the sheets in those cells? And then do I have to define/name that range reference? 2: How does excel recognize that &L1:L5& refers to a different spreadsheet than the one in which I'm working? 3. Is the A: reference for the range in SumIf, and A:31 the criteria and K: the sum_range? The following formula is the first one that I came up with that would only sum for one worksheet. Maybe this will help you see what I was attempting. SUMIF('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23,A29,'[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) When entering this formula I was in a different excel spreadsheet. I got stuck when I attempted to enter a second sheet from Skillman Invoices into the mix for range and sum_range. Any help you could offer would be much appreciated. Thanks for your help so far. -- John S "T. Valko" wrote: Let's assume you have 5 sheets and you want to sum the range B1:B10 on each sheet where the range A1:A10 on each sheet contains the entry "X". If your sheet names are all completely unique (North,East,South,West) then you have to list the sheet names in a range of cells. If the sheet names follow some sort of sequential pattern (Week1,Week2,Week3) then we can do it without listing the sheet names. So, I'll show you how to do it with listing the sheet names. Assume L1:L5 = sheet names =SUMPRODUCT(SUMIF(INDIRECT("'"&L1:L5&"'!A1:A10")," X",INDIRECT("'"&L1:L5&"'!B1:B10"))) -- Biff Microsoft Excel MVP "John S" wrote in message ... Thanks for responding. I need to gather data from more than one worksheet when it meets a particular criteria. In principle I'm trying to match a budget coding number from the master spreadsheet, 'the budget', to the corresponding budget codes from the invoices in another spreadsheet with one worksheet corresponding to one invoice. Once matched then I need to sum the corresponding dollars from the invoices onto the budget spreadsheet. -- John S "T. Valko" wrote: You can't do it like that. Provide more detail. -- Biff Microsoft Excel MVP "John S" wrote in message ... How do I enter more than one worksheet in the range function of the sumif formula and hence in the sum_range function? -- John S |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif-gathering data from more than one worksheet
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "John S" wrote in message ... 'Your the Man!' I have entered the formula in my experimental workbook with a functioning budget and two sample invoices and it worked! It summed for the budget coding number from both invoices. There is no way I could have ever come up with that on my own. I don't know how I can repay you except with a hearty thanks. "Thanks" -- John S "T. Valko" wrote: Ok, with all the sheets in the same file... All sheets following the sequential name pattern: Invoice 1, Invoice 2, Invoice 3, etc. All on one line: =SUMPRODUCT(SUMIF(INDIRECT("'Invoice "& ROW(INDIRECT("1:n"))&"'!A15:A26"),A32,INDIRECT ("'Invoice "&ROW(INDIRECT("1:n"))&"'!K15:K26"))) Where n = the number of the last sheet in the sequence. If you want to do the SUMIF on Invoice 1, Invoice 2, Invoice 3, then n = 3. Note that every sheet referenced must exist. If you have 3 sheets and set n to 5 to allow for future sheets to be added then that would cause an error. Some parts of the formula are kind of cryptic. All those quotes!!! Before Invoice and before ! is a double quote followed by a single quote: " ' Invoice " ' ! At first glance they may look like 3 single quotes: ''', but they're not! -- Biff Microsoft Excel MVP "John S" wrote in message ... I can't express how grateful I am for your time and help. I went ahead and moved the excel sheet that was in a different workbook into the same workbook that the invoices are in, so now in the same workbook I have the budget worksheet and all the invoice worksheets. Based on your reply I entered this formula in the appropriate spot on the budget worksheet: SUMPRODUCT(--('Invoice 1'!A15:A26=A32),('Invoice 1'!K15:K26)) It did work but just for the specified invoice, #1. So thank you. How do I gather the same data for A:32 from the other invoices in different worksheets? -- John S "T. Valko" wrote: Ok, it looks like you want to reference another file. This changes things considerably! There is no way to do this with a single formula. You'll have to use a separate formula for each sheet that you want to reference. Also, the SUMIF function doesn't work when the referenced file is closed. So, based on your posted sample formula: SUMIF('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23,A29, '[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) Try this: =SUMPRODUCT(--('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23=A29),'[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) You'll have to use a formula for each sheet then you can get the grand total from the subtotals. Here's an "easy" way to do this... Have the source file open (Skillman Invoices.xls) In the file where you want the formulas, start typing the formula: =SUMPRODUCT(--( When you get to that point use your mouse and navigate to the source file and source sheet and point to the ranges. Excel will automatically add the source path for you: =SUMPRODUCT(--('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23 Then finish by typing in the rest of the that part of the formula: = A29), Then repeat the mouse pointing process for the other range: '[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) -- Biff Microsoft Excel MVP "John S" wrote in message ... Thanks so much for your help. It would probably help if you knew that I'm very inexperienced in what we are discussing. After digesting what you said I entered this formula: =SUMPRODUCT(SUMIF(INDIRECT(""&L1:L5&"!A12:A23""),A 31,INDIRECT(''"&L1:L5&"!K12:K23""))) Excel responded in various manners when I got to various parts. I barely register what you are explaining so here are my questions. 1. Before assuming L1:L5=Sheet Names do I have to list all the names of the sheets in those cells? And then do I have to define/name that range reference? 2: How does excel recognize that &L1:L5& refers to a different spreadsheet than the one in which I'm working? 3. Is the A: reference for the range in SumIf, and A:31 the criteria and K: the sum_range? The following formula is the first one that I came up with that would only sum for one worksheet. Maybe this will help you see what I was attempting. SUMIF('[Skillman Invoices.xls]Invoice 2'!$A$12:$A$23,A29,'[Skillman Invoices.xls]Invoice 2'!$K$12:$K$23) When entering this formula I was in a different excel spreadsheet. I got stuck when I attempted to enter a second sheet from Skillman Invoices into the mix for range and sum_range. Any help you could offer would be much appreciated. Thanks for your help so far. -- John S "T. Valko" wrote: Let's assume you have 5 sheets and you want to sum the range B1:B10 on each sheet where the range A1:A10 on each sheet contains the entry "X". If your sheet names are all completely unique (North,East,South,West) then you have to list the sheet names in a range of cells. If the sheet names follow some sort of sequential pattern (Week1,Week2,Week3) then we can do it without listing the sheet names. So, I'll show you how to do it with listing the sheet names. Assume L1:L5 = sheet names =SUMPRODUCT(SUMIF(INDIRECT("'"&L1:L5&"'!A1:A10")," X",INDIRECT("'"&L1:L5&"'!B1:B10"))) -- Biff Microsoft Excel MVP "John S" wrote in message ... Thanks for responding. I need to gather data from more than one worksheet when it meets a particular criteria. In principle I'm trying to match a budget coding number from the master spreadsheet, 'the budget', to the corresponding budget codes from the invoices in another spreadsheet with one worksheet corresponding to one invoice. Once matched then I need to sum the corresponding dollars from the invoices onto the budget spreadsheet. -- John S "T. Valko" wrote: You can't do it like that. Provide more detail. -- Biff Microsoft Excel MVP "John S" wrote in message ... How do I enter more than one worksheet in the range function of the sumif formula and hence in the sum_range function? -- John S |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
SUMIF help! | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |