Getting COUNTIF to work with other functions in Excel
Q: I'm trying to do a check on the number of dates in a row which are less than (or equal to) today's date. This figure would then be used in a calculation. I have tried various options, but without success. My target is to have
=COUNTIF(A2:O2,"<=Today")
But I'm getting a 0 result, even though there should be several. I've checked formatting for all dates is correct, and that the Countif is formatted as a number. I must be missing something obvious, but can't see it.
A: The obvious thing you're missing is far from obvious!
Firstly, for some reason, Excel treats TODAY as a function rather than a variable, so it always needs to be followed by an empty set of brackets, thus:
TODAY()
My starting point for the formula was this:
=COUNTIF(A2:O2,"<=TODAY()")
Just like you, I was expecting some hits, but got a zero. I started thinking about the effect of putting a formula inside a string, and realised that it wouldn't work that way. So I built a way of resolving the function outside the string and then tagging the result onto it, like this:
=COUNTIF(A2:O2,"<="&TODAY())
This version actually works like you want.