DCount, with mixed type data

[Daron.Lowell]

First off, here is a sample set...
--------------------------
Database:
MyCode Score Rating
9 641 22
G 649 23
9 675 23
J 649 23
M 999 95
B 529 21

--------------------------
and my Criteria:
Rating MyCode
22 a
23 b
c
--------------------------

I can get a DCOUNT as long as I use a letter for the criteria under
MyCode. What I need to do is get a DCOUNT of everything except the 9's
(<>9) in the criteria. BUT, everytime i put the <>9 in, I get bad
results.

How do I count all the non numeric entries?!?!

 

[Guest]

Use DCOUNTA instead ( or post your formula that does not work if you use
other criteria as well) and then <>9 as criteria (works for me), You know you
can easily do this using other functions, like

=COUNTIF(MyCode,"<>9")

to count <>9 in the MyCoder range and if you want multiple conditions like
testing for rating 23 as well

=SUMPRODUCT(--(MyCode<>9),--(Rating=23))

There is no need really for the somewhat clumsy D functions, that was
probably just to be compatible with Lotus


Regards,

Peo Sjoblom