Countif using a cell reference as range

[J.W. Aldridge]

=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

 

[Don Guillett]

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

 

[T. Valko]

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

Try it like this:

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

 

[Pete_UK]

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

 

[Infinitogool]

Hi J.W. Aldridge

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

Regards,
Pedro J.

 

[J.W. Aldridge]

This one worked!

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



Thanx ALL!

 

[Pete_UK]

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