Thread: COUNTIF will not read date from reference cell

Hi and welcome to the forum.

Try it like this:

=COUNTIF(RFI!O:O,"<"&F5)

Sure...

& is the concatenation operator: it joins strings together. So, for example:

"hello " & "the" & " world"
becomes:
"hello the world"

With your original try:
=COUNTIF(RFI!O:O,"<F5")

COUNTIF is trying to count any cells which have a value less than the string "F5". It is not trying to count any cells which have a value less than the value in the F5 cell. It doesn't realise that you want it to refer to cell F5, because "F5" is a string, not a range reference.

To work around this, you can concatenate the string "<" to the value in the F5 cell, like this:
"<"&F5


In this case, F5 contains the date 27 June 2011. In Excel, dates are stored as numbers under the hood, and I expect that the numeric equivalent of 27 June 2011 in your Excel will be 40721.

So, if F5 contains the date 6-27-11, "<"&F5 would return this string: "<40721".
COUNTIF can then correctly assess column A for any values which are smaller than 40721.