Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel DCOUNTA problem 2

Status
Not open for further replies.

ETID

Programmer
Jul 6, 2001
1,867
US
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...



 
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

:(
 
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
 
Thanks Roy-Vidar

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

 
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]
 
It is an issue with how you build the criteria...

If your formula is set up as follows:
Code:
=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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top