=COUNTIF(formalin_count,"<=d$2")

formalin_count consist of a string of times (F6:F64999)
ie 07:00 - 19:00

d2 consist of a time (the end/max time). (18:00)

I am trying to get the count of instances up until the time given in
D2.

The question is how to get this to read the reference D$2 ?

thanx

try this idea
=COUNTIF(A:A,"<" &d2)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:3dc06f0b-cc5b-4899-9d0e-(E-Mail Removed)...
> =COUNTIF(formalin_count,"<=d$2")
>
> formalin_count consist of a string of times (F6:F64999)
> ie 07:00 - 19:00
>
> d2 consist of a time (the end/max time). (18:00)
>
> I am trying to get the count of instances up until the time given in
> D2.
>
> The question is how to get this to read the reference D$2 ?
>
> thanx
>
>

>=COUNTIF(formalin_count,"<=d$2")

Try it like this:

=COUNTIF(formalin_count,"<="&D$2)

--
Biff
Microsoft Excel MVP


"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:3dc06f0b-cc5b-4899-9d0e-(E-Mail Removed)...
> =COUNTIF(formalin_count,"<=d$2")
>
> formalin_count consist of a string of times (F6:F64999)
> ie 07:00 - 19:00
>
> d2 consist of a time (the end/max time). (18:00)
>
> I am trying to get the count of instances up until the time given in
> D2.
>
> The question is how to get this to read the reference D$2 ?
>
> thanx
>
>

Do you mean that you want to count if the time in D2 falls within the
range of times in each cell in formalin_count? What if the time is
equal to the end time in the range? Presumably if the range is 18:00 -
21:00 this would not count?

Pete

On Aug 7, 10:16*pm, "J.W. Aldridge" <jeremy.w.aldri...@gmail.com>
wrote:
> =COUNTIF(formalin_count,"<=d$2")
>
> formalin_count consist of a string of times (F6:F64999)
> ie *07:00 - 19:00
>
> d2 consist of a time (the end/max time). (18:00)
>
> I am trying to get the count of instances up until the time given in
> D2.
>
> The question is how to get this to read the reference D$2 ?
>
> thanx

Hi J.W. Aldridge

Try something like this:
=COUNTIF(formalin_count,"<="&d$2)

Regards,
Pedro J.

> =COUNTIF(formalin_count,"<=d$2")
>
> formalin_count consist of a string of times (F6:F64999)
> ie 07:00 - 19:00
>
> d2 consist of a time (the end/max time). (18:00)
>
> I am trying to get the count of instances up until the time given in
> D2.
>
> The question is how to get this to read the reference D$2 ?
>
> thanx
>
>

This one worked!

=COUNTIF(formalin_count,"<="&D$2)



Thanx ALL!

If F6, the first cell in the range formalin_count, contains "07:00 -
19:00" (i.e. a text value) and D2 contains 18:00 (a time value) then
you won't be able to use COUNTIF directly. You'll have to extract the
start and end times out of F6 and then see if D2 is within the range
(if that's what you are trying to do - see my earlier questions). This
will probably be a sumproduct formula.

Pete

On Aug 7, 10:16*pm, "J.W. Aldridge" <jeremy.w.aldri...@gmail.com>
wrote:
> =COUNTIF(formalin_count,"<=d$2")
>
> formalin_count consist of a string of times (F6:F64999)
> ie *07:00 - 19:00
>
> d2 consist of a time (the end/max time). (18:00)
>
> I am trying to get the count of instances up until the time given in
> D2.
>
> The question is how to get this to read the reference D$2 ?
>
> thanx