Indirect ref in array formulas

[Guest]

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

 

[Don Guillett]

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

 

[Guest]

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

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

 

[Don Guillett]

glad to help