About Us

Don Guillett

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
defintion of array function	R..VENKATARAMAN	Excel Discussion (Misc queries)	2	April 21st 06 03:21 AM
array formulas	Dave	Excel Worksheet Functions	2	March 17th 06 04:41 PM
Array Formula Not Working with Range with Formulas	[email protected]	Excel Discussion (Misc queries)	4	February 1st 06 02:01 PM
array formulas	R.VENKATARAMAN	Excel Worksheet Functions	7	June 16th 05 10:25 PM
array formulas	johnT	Excel Worksheet Functions	14	March 29th 05 08:35 AM

Menu

About Us