DCOUNTA Problems - Bug?

[daveatthewell]

I have an Excel database as a named range on one worksheet called "database". Each record in the database contains a RecordID (not used), a "unique" userID and a date as well as other data of no immediate interest here. So it might look like this:
ID Advisor Date
27 FRED 21-Jun-07
28 FRED1 21-Jun-07
29 ARTHUR 21-Jun-07
31 FRED 21-Jun-07
32 ARTHUR 21-Jun-07
33 FRED 21-Jun-07
34 FRED1 21-Jun-07
To extract the data to a report I'm using the construct:
Advisor Date Date Advisor
FRED >=39234 <=39263 =DCOUNTA(Database,F7,$C7:$E8)

where Advisor is in C7, "from" Date is in D7 and "to" Date is in E7 and F7 is immediately above the formula and contains Advisor

I would expect this formula to return the number of calls logged by Fred between 1 and 30 June 2007. Assuming, for now, the only data in the database is the above 7 records I would expect the answer to be 3; the answer is 5 as DCOUNTA seems to want to count calls from FRED and FRED1.
Now I have no control over the advisor UserID - it is unique and no two people can have the same - but obviously, as it is derived from surnames (the above is obviously not, it's purely for demo purposes), there will be possibilities that userids could include parts of others. Is this a bug, or "Is it me!!"
Regards
David Kennedy
in a bright and sunny Scotland.

 

[pgc01]

Hi David

That's the way it works, it will count any advisor whose name starts with FRED. You may try with just A and it will count 2 (the 2 ARTHURs).

If you want an exact match, use as criterion:

="=FRED"

 

[daveatthewell]

Thanks for that - it works fine now. But I can't help feeling that DCOUNT's help function should point this out. It seems to be behaving in a slightly similar fashion to VLookup/Match where, unless you specify an exact match (ie logical 0), you get a different result from that which was expected.
Anyway, it's done and thanks again.

David