Count if Dates are!
 

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

Count if Dates are!
 

Try this:

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

Format as GENERAL or NUMBER

--
Biff
Microsoft Excel MVP


"Jman" wrote in message
...
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

Count if Dates are!
 

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" wrote in message
...
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

Count if Dates are!
 

Exactly what i wanted.
Thanks.

"T. Valko" wrote:

Try this:

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

Format as GENERAL or NUMBER

--
Biff
Microsoft Excel MVP


"Jman" wrote in message
...
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

Count if Dates are!
 

Thanks for your reply.
"Fred Smith" wrote:

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" wrote in message
...
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

Count if Dates are!
 

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

"T. Valko" wrote:

Try this:

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

Format as GENERAL or NUMBER

--
Biff
Microsoft Excel MVP


"Jman" wrote in message
...
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

Count if Dates are!
 

You're welcome!

Now, let's get that other question solved!

--
Biff
Microsoft Excel MVP


"Jman" wrote in message
...
Exactly what i wanted.
Thanks.

"T. Valko" wrote:

Try this:

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

Format as GENERAL or NUMBER

--
Biff
Microsoft Excel MVP


"Jman" wrote in message
...
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