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

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

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

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

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

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