Indirect Ref in an Array formula
I have a workbook with many worksheets, each with columns of inventory
movement transactions on it. This data is consolidated on a single summary
worksheet. In my summary sheet, I am using sumif formulas to sum the data
according to the type of transaction from each transaction sheet. The ranges
in the sumif use "indirect" functions to point to the transaction sheets
which are dynamically named the same as the inventory items which are listed
in column B of my summary sheet. I use the formula
=SUMIF(INDIRECT("'"&$B6&"'!E:E"),$S$3,INDIRECT("'" &$B6&"'!G:G")). ($S$3 being
the transaction type).

The problem I have is that Now I have to add start and end date criteria to
conditional summing. I have tried using multiple sum functions in an array
formula to get my multiple criteria but Indirect doesn't seem to work inside
an array the way I have it. Anyone know how to get a multiple criteria
conditional sum with indirect range/sheet references? I realize using a lot
(1000's) of array formulas and indirect functions are likely to bog down the
recalc time quite a bit so if there is another more efficient way to do it, I
would be happy to hear it.
Thanks in advance
RDW

use the sumproduct idea where a has the dates and b is the conditional and c
is the column to sum
=sumproduct((a4:a22a2)*(a4:a22<a3)*(b2:b22=$s$3)* c2:c22)
note it will NOT work with entire columns

--
Don Guillett
SalesAid Software

"RD Wirr" wrote in message
...
Indirect Ref in an Array formula
I have a workbook with many worksheets, each with columns of inventory
movement transactions on it. This data is consolidated on a single summary
worksheet. In my summary sheet, I am using sumif formulas to sum the data
according to the type of transaction from each transaction sheet. The
ranges
in the sumif use "indirect" functions to point to the transaction sheets
which are dynamically named the same as the inventory items which are
listed
in column B of my summary sheet. I use the formula
=SUMIF(INDIRECT("'"&$B6&"'!E:E"),$S$3,INDIRECT("'" &$B6&"'!G:G")). ($S$3
being
the transaction type).

The problem I have is that Now I have to add start and end date criteria
to
conditional summing. I have tried using multiple sum functions in an array
formula to get my multiple criteria but Indirect doesn't seem to work
inside
an array the way I have it. Anyone know how to get a multiple criteria
conditional sum with indirect range/sheet references? I realize using a
lot
(1000's) of array formulas and indirect functions are likely to bog down
the
recalc time quite a bit so if there is another more efficient way to do
it, I
would be happy to hear it.
Thanks in advance
RDW

That worked like a champ, Don.
Many thanks,
RDW

"Don Guillett" wrote:

use the sumproduct idea where a has the dates and b is the conditional and c
is the column to sum
=sumproduct((a4:a22a2)*(a4:a22<a3)*(b2:b22=$s$3)* c2:c22)
note it will NOT work with entire columns

--
Don Guillett
SalesAid Software

"RD Wirr" wrote in message
...
Indirect Ref in an Array formula
I have a workbook with many worksheets, each with columns of inventory
movement transactions on it. This data is consolidated on a single summary
worksheet. In my summary sheet, I am using sumif formulas to sum the data
according to the type of transaction from each transaction sheet. The
ranges
in the sumif use "indirect" functions to point to the transaction sheets
which are dynamically named the same as the inventory items which are
listed
in column B of my summary sheet. I use the formula
=SUMIF(INDIRECT("'"&$B6&"'!E:E"),$S$3,INDIRECT("'" &$B6&"'!G:G")). ($S$3
being
the transaction type).

The problem I have is that Now I have to add start and end date criteria
to
conditional summing. I have tried using multiple sum functions in an array
formula to get my multiple criteria but Indirect doesn't seem to work
inside
an array the way I have it. Anyone know how to get a multiple criteria
conditional sum with indirect range/sheet references? I realize using a
lot
(1000's) of array formulas and indirect functions are likely to bog down
the
recalc time quite a bit so if there is another more efficient way to do
it, I
would be happy to hear it.
Thanks in advance
RDW

glad to help

--
Don Guillett
SalesAid Software

"RD Wirr" wrote in message
...
That worked like a champ, Don.
Many thanks,
RDW

"Don Guillett" wrote:

use the sumproduct idea where a has the dates and b is the conditional
and c
is the column to sum
=sumproduct((a4:a22a2)*(a4:a22<a3)*(b2:b22=$s$3)* c2:c22)
note it will NOT work with entire columns

--
Don Guillett
SalesAid Software

"RD Wirr" wrote in message
...
Indirect Ref in an Array formula
I have a workbook with many worksheets, each with columns of inventory
movement transactions on it. This data is consolidated on a single
summary
worksheet. In my summary sheet, I am using sumif formulas to sum the
data
according to the type of transaction from each transaction sheet. The
ranges
in the sumif use "indirect" functions to point to the transaction
sheets
which are dynamically named the same as the inventory items which are
listed
in column B of my summary sheet. I use the formula
=SUMIF(INDIRECT("'"&$B6&"'!E:E"),$S$3,INDIRECT("'" &$B6&"'!G:G")). ($S$3
being
the transaction type).

The problem I have is that Now I have to add start and end date
criteria
to
conditional summing. I have tried using multiple sum functions in an
array
formula to get my multiple criteria but Indirect doesn't seem to work
inside
an array the way I have it. Anyone know how to get a multiple criteria
conditional sum with indirect range/sheet references? I realize using a
lot
(1000's) of array formulas and indirect functions are likely to bog
down
the
recalc time quite a bit so if there is another more efficient way to do
it, I
would be happy to hear it.
Thanks in advance
RDW