J
John S
How do I enter more than one worksheet in the range function of the sumif
formula
and hence in the sum_range function?
formula
and hence in the sum_range function?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
T
T. Valko
You can't do it like that. Provide more detail.
J
John S
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.
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.
T
T. Valko
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")))
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")))
J
John S
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""),A31,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.
very inexperienced in what we are discussing. After digesting what you said
I entered this formula:
=SUMPRODUCT(SUMIF(INDIRECT(""&L1:L5&"!A12:A23""),A31,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.
T
T. Valko
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:
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
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 said: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""),A31,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 said: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")))
J
John S
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
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 said: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 said: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""),A31,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 said: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
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
:
You can't do it like that. Provide more detail.
--
Biff
Microsoft Excel MVP
How do I enter more than one worksheet in the range function of the
sumif
formula
and hence in the sum_range function?
T
T. Valko
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
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 said: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 said: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 said: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""),A31,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
:
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
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
:
You can't do it like that. Provide more detail.
--
Biff
Microsoft Excel MVP
How do I enter more than one worksheet in the range function of
the
sumif
formula
and hence in the sum_range function?
J
John S
'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
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 said: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 said: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 said: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
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""),A31,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
:
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
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
:
You can't do it like that. Provide more detail.
--
Biff
Microsoft Excel MVP
How do I enter more than one worksheet in the range function of
the
sumif
formula
and hence in the sum_range function?
T
T. Valko
You're welcome. Thanks for the feedback!
--
Biff
Microsoft Excel MVP
--
Biff
Microsoft Excel MVP
John S said:'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 said: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 said: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
:
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
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""),A31,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
:
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
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
:
You can't do it like that. Provide more detail.
--
Biff
Microsoft Excel MVP
How do I enter more than one worksheet in the range function
of
the
sumif
formula
and hence in the sum_range function?
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.