DCOUNT Performance

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi - Looking for performance enhancement advise.

Hi - I am using DCOUNTA to calculate both a total and a pass in a
calculation to workout percentage passed, based on several filters in a sheet
of data which is 20,000 records.

I have a total of 230 DCOUNTA's and all using the same filter names, and
each DCOUNTA has 10 columns of 'and' filters and between 2 to 7 rows of 'or'
filters.

I have a look up that changes one of the filter values in all DCOUNTA's and
when I make the selection it takes 1min 34sec to recalculate.

Is this normal and expected or is there a better method or syntax ?

Typical DCOUNTA is =DCOUNTA('Incident Data'!$A:$Z,1,A5:K9).

one filter field has =IF(FixFilter_Name="","",FixFilter_Value) and there are
a total of 330 of these.


Any ideas on how to reduce the calculation time?
 
1 min 34 seconds is not that bad considering you have 20,000 records, if you
were to use array formulas my guess is that it would be slower compared to
using a D function. The only thing I could think of that would improve with
the exception of using a database and not Excel for something like this
would be to use many small formulas and then sum or count the results of
those formulas. You could turn off automatic calculation so it doesn't
calculate when you change a lookup value then when it is time to refill the
coffee manually calculate the workbook.
 
Paul Dennis said:
I have a total of 230 DCOUNTA's and all using the same filter names, and
each DCOUNTA has 10 columns of 'and' filters and between 2 to 7 rows of
'or' filters.
Click to expand...
....

If there were a fair amount of redundancy, row to row, in your criteria
range, there could be some performance gains. Impossible to be more precise
without seeing what the full criteria range looks like.
 
Back
Top