DCOUNTA Performance

[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?

 

[Douglas J. Steele]

This newsgroup is intended for questions about Access, the database product
that's part of Office Professional.

There is no DCOUNTA function in Access, so I have to assume you're talking
about Excel. If that's the case, I'd recommend reposting your question to a
newsgroup related to Excel.

 

[John W. Vinson]

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.

It sounds like you're using Excel; you may want to repost your question in an
Excel newsgroup. This group is for a different program, Microsoft Access. The
webpage can be confusing - scroll down the list of subject areas and find one
for Excel.

John W. Vinson [MVP]

 

[Jeff Boyce]

Given the cell references in your formula, I'm going to guess you are
working in Excel.

This newsgroup is dedicated to Microsoft Access, the relational database
product. Post to an Excel-specific newsgroup for help with Excel.

What you are describing, though, seems like it would be fairly simple to do
with a query in Access... <g>

Regards

Jeff Boyce
Microsoft Office/Access MVP