Forum Discussion
COUNTIF with dates
The way you have coded the formula
=COUNTIF(E3:E55,">=D59")
makes the D59 a literal text string and not a cell reference. Try
=COUNTIF(E3:E55,">="&D59)
Peter and Sergei - thank you so much, it worked.
Is there a way to nest the TODAY function in the COUNTIF argument and not reference cell D59? Just curious.
And if you are on Excel 365 it could be
=SUM(--(E3:E55>=TODAY()) )Double dash is needed to convert logical TRUE or FALSE into 1 or 0.
At various times, I have used
= SUM(SIGN(Boolean#)) 'The help page doesn't even mention Booleans = SUM(+N(Boolean#)) 'Documented but does not accept arrays = SUM(--Boolean#) 'Mathematically obscure syntax that would appear to be a null operatorand have still to commit to one. Is there an 'officially approved' strategy?
PeterBartholomew1 , I don't think any 'officially approved' strategy exists. With SIGN that's a good finding, but from my point of view is not practical for such purposes. If collect variants you forgot
= SUM(Boolean#+0) = SUM(Boolean#*1) = SUM(Boolean#*Boolean#) = -SUM(-Boolean#)Perhaps something else, that's Excel
Yes, you can nest TODAY, or other functions, to build a COUNTIF criterion
=COUNTIF(E3:E55,">="&TODAY())
It is also possible to 'hard-wire' the number as text within the criterion string
=COUNTIF(E3:E55,">=44028")
=COUNTIF(E3:E55,">=16/07/2020")
What (sadly) is not allowed is to use a function within the criterion range, e.g.
=COUNTIF(MONTH(E3:E55),">6")
because that converts a range reference into an array.
