Jul 16 2020 07:59 AM
We have students checking in various dates. I have a column of check-in dates and would like to know how many have checked in as of today. Set a cell (D59) for today's date (=TODAY()) and used =COUNTIF(E3:E55,">=D59") and it returns a value of zero. Can I do this with COUNTIF, or is there another function I should use?
Also, can I use the TODAY function in the COUNTIF function instead referring to the other cell? What would that look like? Thanks for your help.
Jul 16 2020 08:16 AM
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)
Jul 16 2020 09:52 AM
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.
Jul 16 2020 10:22 AM
Hello, @markyehl
My understanding of challenge posted here is as follows,
If my understanding is correct, then formula should be as below, included today function in formula. Also please find attached sample sheet.
Hope this will of help to you.
Jul 16 2020 11:52 AM
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.
Jul 16 2020 02:42 PM
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.
Jul 17 2020 06:47 AM - edited Jul 17 2020 07:00 AM
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 operator
and have still to commit to one. Is there an 'officially approved' strategy?
Jul 17 2020 10:27 AM
@Peter Bartholomew , 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
Jul 17 2020 12:10 PM
Jul 17 2020 01:05 PM
@Sergei Baklan basically your examples here are that any math action will prompt Excel to try to convert the values, in this case Boolean, to a number. The very popular "--" forces a negate and then negate again so you end up with the "original value" as a value instead of text or Boolean. I suspect its popularity is that it is probably the most compact way to force a conversion and result in the "same" value and is visually unobtrusive. I'm sure you two already know all this but thought I would add it for others that might read this thread.
Jul 17 2020 01:38 PM
Yes, double dash is most popular as the fastest way from typing point of view. And at the same time is most confusing one, Since quite many people don't know about the difference between negation and subtraction, Excel designers didn't find separate character for the negation in ANSII table. Thus people are surprised why =-Booleans# works, but =+Booleans# not; or why =-6^2 returns plus 36, not minus 36.
Jul 17 2020 01:42 PM
You are right; I probably should have referred to the more positive features of '--' rather than limiting my comment to the more negative 'mathematically obscure'. I do remember being disoriented by it, though, when I first encountered the notation in a SUMPRODUCT, but one gets used to it.
Jul 17 2020 01:51 PM
Some ago (long ago) I shifted in SUMPRODUCT() on *1 since had couple of cases when people removed double dash considering minus on minus in any case returns plus, and two minuses only complicate the formula, But now I'm again mostly on double dash.