OFFSET is volatile. This means that every formula that contains OFFSET or is
dependent on a formula that contains OFFSET will always calculate at every
recalculation.
COUNTA is quite slow because it scans every cell in the used range of the
column you are counting.
Array formula are quite slow because each formula requires the creation and
calculation of virtual columns/rows, and all of the formulae in these
virtual columns/rows have to be calculated each time the array formulae is
calculated.

The solution is one or more of
- use a completely different approach (ie pivot tables or do your filtering
in ACCESS or ...)
- minimise the amount of repeated calculation by using helper cells/columns
(only do your COUNTA once, only do Database!$C$3:$C$2500=$B$3 once etc)
- sort your data and do subset calculations
- use more efficient formulae (DSUM, SUMPRODUCT, ...)

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Karl Burrows" wrote in message
...
I posted this earlier, but it went in a different direction...

I have several workbooks where I replaced array formulas with named ranges
to pull data from a tab that imports data from an Access database. There
were several thousand of these formulas. An example would be:


=IF($D$97<=E$11,IF($B$5=E$12,SUM(IF((Subdivision= $B$3)*(Builder=$B28)*(Date
=E$9)*(Date<F$9),(Count),0)),""),"")

I created the names since I wanted to have a dynamic range of values to
search since the database would return varying rows of data depending on
what was input in the database:

=OFFSET(Database!$E$3,0,0,COUNTA(Database!$E:$E),1 )

When I replaced the original formulas:


=IF($D$97<=E$11,IF($B$5=E$12,SUM(IF((Database!$C$ 3:$C$2500=$B$3)*(Database!

$D$3:$D$2500=$B28)*(Database!$E$3:$E$2500=E$9)*(D atabase!$E$3:$E$2500<=F$9)
,(Database!$E$3:$E$2500),0)),""),""))

with the named ranges, the workbooks have ground to a halt. It now takes
forever for them to update (they weren't speed demons in the first place
with so many arrays) and Macros run incredibly slow (5 minutes or more to
run!).

Did I do something wrong? Are there limitations to named ranges? Is
there
something with a dynamically named range that would create this problem?
I
thought I was trying to make the workbooks more efficient and allow for an
expanding range of data without having to 'anticipate' how many rows to
include in the array making less monitoring for me.

Help! Thanks!