Excel DCOUNTA problem 2

[ETID]

Hi All,

Has any noticed that the DCOUNT and DCOUNTA functions do not work correctly if one of the data items to count in the source is only 1 alpha character in length?

If so is there a work around or fix?

The functions ignore the single character items defined in the criteria and count all items in the database range.

If I change the critera to count an item that is 2 or more characters in length the counts are true.

I have confirmed that this "feature/anomaly" is present in both office Xp and office 2K version of Excel.

Thanks for any input...

 

[BenRowe]

It works for me. I used a simple list of items.

 

[ETID]

hmm I too used a simple list after I discovered this...

the one field in my list of three fields 100 rows is a classification, it can be A, AA ,AAA B,AB Etc.

but dcounta cannot find the single A's or B's !!??!??

...using excel 2k or XP

 

[RoyVidar]

Yesterday I verified your findings.

Then today, first I did not, then...

When entering information as a, aa, abc, asd, qwe, sdf... and using the criterion a, it counted all occurrences starting with the letter a, like a* in a database??? But then when trying other single characters, it went back to counting none.

But why, and how to work around, I don't know.

Roy-Vidar

 

[ETID]

Thanks Roy-Vidar

I am trying a variety of formats in an attempt to force a valid search, but to no avail as yet.

 

[SilentAiche]

Is case sensitivity an issue here?

Just a thought (that's most likely already been thought of!)

Tim

[blue]__________________________________________________
If you need immediate assistance, please raise your hand.[/blue]

 

[xlbo]

It is an issue with how you build the criteria...

If your formula is set up as follows:

=DCOUNTA("A1:A100","List","B1:B2")

where your data is in a1:A100, with "List" in A1 and your criteria set up with "List" in B1 & A in B2, you will return all results that include an "A" anywhere in them

To return only those with a single "A" on its own, you must enter

="=A" into cell B2

This will result in the text =A being shown in the cell. This will do as you require. It works this way as for numeric values, the criteria would look like:
>1000
or
<=5000 etc etc...
so excel is expeting to see =A rather than just A in the cell to get exact matches. If you leave out any qualifier, excel will assume wildcards on either side of it - this is what causes the results you have been seeing.

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[ETID]

Thanks to everyone for their input, and thanks to XLBO for the solution...I was trying to build a similar string with contatenation but I left out the leading "=" in the expression.