"Why" is a hard question to answer in this case!

I don't know "why" other than to say that's how the programmers that
developed the formula parser wrote it to work.

When using a comparison operator and referring to a reference (which might
also be another function) You have to concatenate the operator to the
reference (with certain functions, COUNTIF being one of those):

=COUNTIF(A1:A5,"="&C1)
=COUNTIF(A1:A5,"="&DATE(2008,5,12))

If you tried this:

=COUNTIF(A1:A5,"=C1")

Then it evaluates "=C1" as the literal TEXT string =C1

But, if the comparison criteria is a hard coded constant you just enclose
both the operator and criteria in quotes (although concatenation will still
work):

=COUNTIF(A1:A5,"=10")
=COUNTIF(A1:A5,"="&10)

When testing for equality then no operator is required but it still works if
you do include it:

=COUNTIF(A1:A10,10)
=COUNTIF(A1:A10,"=10")
=COUNTIF(A1:A10,"="&10)

These are nuances of Excel that you learn and get used to over time!


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi Biff,
Just jumping in...
I tried your formula - it works, of course :)
Could you please explain to me why the operators need to be in double
quotes, and why we need the & thingy?
Regards - Dave.

"T. Valko" wrote:

Try this:

C1 = 5/12/2008
D1 = 5/25/2008

=COUNTIF(A1:A5,"="&C1)-COUNTIF(A1:A5,""&D1)

Format as GENERAL or NUMBER

--
Biff
Microsoft Excel MVP