DCOUNT function not working properly on string comparisons
 

I have a worksheet that contains some raw data. In nne of the columns of raw
data (let's call it "Code") all of the values are formatted as text. Some
example values in this column a K4, K5, 11, K9. In another worksheet I
have a series of DCOUNT functions that perform counts on the raw data based
on different criteria. One such function counts rows that do not have a
"Code" of 11 or K5. So the DCOUNT criteria looks like:

Code
<11

Code
<K5

You get the idea. Problem is it is not filtering out the Codes of 11
because they are formatted as strings (i.e. '11). It is not an option to
format them as numbers.

Weird thing is that =11 works just fine. I also got an answer from someone
saying to include the column header in the 2nd cell of criteria like so:

<blank cell
Code<11

Code
<K5

This works, but is not acceptable because the users of the workbook need to
be able to easily modify criteria to change counts and this confuses that
process. Is there any way to get this to work using the normal DCOUNT
criteria format?

DCOUNT function not working properly on string comparisons
 

Hi

Try making your test
CODE<"11"

--
Regards

Roger Govier


"MJP" wrote in message
...
I have a worksheet that contains some raw data. In nne of the columns
of raw
data (let's call it "Code") all of the values are formatted as text.
Some
example values in this column a K4, K5, 11, K9. In another
worksheet I
have a series of DCOUNT functions that perform counts on the raw data
based
on different criteria. One such function counts rows that do not have
a
"Code" of 11 or K5. So the DCOUNT criteria looks like:

Code
<11

Code
<K5

You get the idea. Problem is it is not filtering out the Codes of 11
because they are formatted as strings (i.e. '11). It is not an option
to
format them as numbers.

Weird thing is that =11 works just fine. I also got an answer from
someone
saying to include the column header in the 2nd cell of criteria like
so:

<blank cell
Code<11

Code
<K5

This works, but is not acceptable because the users of the workbook
need to
be able to easily modify criteria to change counts and this confuses
that
process. Is there any way to get this to work using the normal DCOUNT
criteria format?