You're welcome!

--
Biff
Microsoft Excel MVP


"Dano" wrote in message
...
Alright that works perfect!
Thanks again!

"T. Valko" wrote:

Try this:

G1 = 5/12/2008
H1 = 5/25/2008
I1 = Done

=SUMPRODUCT(--(A1:A5=G1),--(A1:A5<=H1),--(E1:E5=I1))

--
Biff
Microsoft Excel MVP


"Dano" wrote in message
...
Ok I have another question with this same thing here . . .
Say the dates in A1 thru A5 remain the same. I add the following
column
:
E1 = "Done"
E2 = "Open"
E3 = "Open"
E4 = "Done"
E5 = "Done"

and I want to know how many cells in this range are earlier than or
equal
to
5/25/08 but later than or equal to 5/12/08 and only count the cells
that
have
"Done" in column E? Should be 2 but how would you constuct a formula
for
that?

Thanks a lot for your help.

Dan



"Dano" wrote:

Fellas thanks a lot that will help me out!

Dan

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi Biff,
Thanks for your detailed reply. Countif is amazingly useful, and
it's
good
to know its "nuances"
Regards - Dave.

"T. Valko" wrote:

"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