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

Try this:

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

Format as GENERAL or NUMBER

--
Biff
Microsoft Excel MVP


"Jman" <(E-Mail Removed)> wrote in message
news:54E9CF7A-11B4-40FB-A81D-(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
news:54E9CF7A-11B4-40FB-A81D-(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
> news:54E9CF7A-11B4-40FB-A81D-(E-Mail Removed)...
> > 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
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
> news:54E9CF7A-11B4-40FB-A81D-(E-Mail Removed)...
> > 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
> >
>
>

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" <(E-Mail Removed)> wrote in message
> news:54E9CF7A-11B4-40FB-A81D-(E-Mail Removed)...
> > 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
> >
>
>
>

You're welcome!

Now, let's get that other question solved!

--
Biff
Microsoft Excel MVP


"Jman" <(E-Mail Removed)> wrote in message
news:7017E38B-728E-476C-89B9-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:54E9CF7A-11B4-40FB-A81D-(E-Mail Removed)...
>> > 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
>> >
>>
>>
>>