Limitations on Dcounta

[speric07]

Hi,

I was wondering whether there were any limitations on using a Dcounta, currently I have seven filters all working I try to add an eigth one but it does not work.

I have tried more than once, the code/formula's are exactly the same.

The only thing I can think of is that there is a limit like with nested IF's.

Please can any one confirm? If so does anyone know a work around.

Many thanks

 

[Andrew Poulsom]

Can you post your formula?

 

[speric07]

Sorry,

Here it is,

=Dcounta(alldatafromstatus,"Total Score",upto0)

alldatafromstatus = Looks at another sheet in the workbook
totalscore = a field in alldatafromstatus
upto0 = the criteria

The formula has been working it is just on the eigth filter it does not work.

Many thanks

 

[Andrew Poulsom]

What does the name upto0 refer to (before and after)?

 

[speric07]

it refers to the criteria that the dcounta loks up.

The code is the same, the layout is the same it just seems there is a limit in Excel.

My dcounta table is 15 columns across so there are 15 criteria ranges being looked up.

Thanks

 

[Andrew Poulsom]

I mean what does the name upto0 refer to in the Define Name dialog box (Insert|Name|Define) - when your formula works and when it no longer works?

 

[speric07]

upto0 is still including all the columns.

I have been inserting the columns from inside the named range so the new filters are still included, without having to redefine the named range.

The only difference is

=sheet1!$W$4:$AK$5 works

=sheet1!$W$4:$AM$5 does not work

 

[Andrew Poulsom]

Sorry, this is still very unclear. Originally you said "I have seven filters all working I try to add an eigth one but it does not work". But W:AK is 15 columns and W:AM is 17 columns.

And in what way doesn't it work?

 

[speric07]

Hi, it worked at home last night, so I assume it is a system limitation on my work PC/Lack of.

Thanks for your willingness to help though Andrew.