Count if Dates are!

[Jman]

In sheet 1 i have
C1 to C30000 "dates.
4/30/08
3/10/08
3/04/08
1/03/05
3/16/08
ect

In sheet 2


I have
A1: this date 3/1/08
A2: this date 3/15/08

I want a formula in C5 that will look into sheet one and see if any dates i
i put in A:1 to A3000 falls on or in between the dates in sheet 2. "3/1/08"
and " 3/15/08"
and if so.. to count as 1

From the example above the aswer that i am lookin for should be 2 since
there are only two dates that fall in between and they are 3/10/08
3/04/08

 

[T. Valko]

Try this:

=COUNTIF(C1:C3000,">="&Sheet2!A1)-COUNTIF(C1:C3000,">"&Sheet2!A2)

Format as GENERAL or NUMBER

 

[Fred Smith]

As I read it, all dates between 3/1/08 and 3/15/08 are to count as 1. So
count them as zero and add one at the end. Your formula would then look
like:

(Dates < 3/1/08) + (Dates > 3/15/08) + 1
=countif(c1:c3000,"<"&a1)+countif(c1:c3000,">"&a2)+1

If you're looking to count *only* the dates between a1 and a2, you want:

=count(c1:c3000)-countif(c1:c3000,"<"&a1)-countif(c1:c3000,">"&a2)

Does this help?

Regards,
Fred.

 

[Jman]

Exactly what i wanted.
Thanks.

Try this:

=COUNTIF(C1:C3000,">="&Sheet2!A1)-COUNTIF(C1:C3000,">"&Sheet2!A2)

Format as GENERAL or NUMBER

 

[Jman]

Thanks for your reply.

As I read it, all dates between 3/1/08 and 3/15/08 are to count as 1. So
count them as zero and add one at the end. Your formula would then look
like:

(Dates < 3/1/08) + (Dates > 3/15/08) + 1
=countif(c1:c3000,"<"&a1)+countif(c1:c3000,">"&a2)+1

If you're looking to count *only* the dates between a1 and a2, you want:

=count(c1:c3000)-countif(c1:c3000,"<"&a1)-countif(c1:c3000,">"&a2)

Does this help?

Regards,
Fred.

 

[Mike]

Maybe this will help
=TEXT(COUNTIF(Sheet1!C1:C3000,">="&A1)-COUNTIF(Sheet1!C1:C3000,">"&A2),0)

 

[T. Valko]

You're welcome!

Now, let's get that other question solved!