DCOUNT function not working properly on string comparisons

[MJP]

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 are: 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?

 

[Roger Govier]

Hi

Try making your test
CODE<>"11"