Count if today and multiple criteria
Hello community,
My formula needs to read data from two columns with the combination of a CountIfs, Distinct, Today formula I think.
On row 16 in the count column, I want the cell to count how many entries are in the Amsterdam column when it is today's date. Row 2 shows today's date (16th March) and the formula should result in a count of 3. The 17th has 1 entry so should result in a count of 1.
If anyone has a solution, let me know!
Best Answer

Starting from the inside, the purple () are part of the TODAY function. TODAY() and TODAY(0) both mean today in UTC time. TODAY(1) means yesterday, TODAY(3) is three days after today, and so on.
The green parentheses enclose the COLLECT function.
The red parentheses enclose the INDEX function. The ,1 at the end of the INDEX function is the row index, meaning give me the first row you find that meets the conditions. So it comes after the embedded COLLECT is closed off, but before closing off the INDEX.
And of course the yellow parentheses enclose the COUNTM function, telling it to use COUNTM on the cell that's returned by the INDEX/COLLECT.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

I think you want to use the COUNTM function with an INDEX/COLLECT for this one:
=COUNTM(INDEX(COLLECT(Amsterdam:Amsterdam, Date:Date, =TODAY()), 1))
The logic is: Collect the cell from the Amsterdam column for the row where the Date equals Today, and count the number of elements in that cell.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Hi Jeff, that worked, thanks!!!!

Could I also ask the logic behind the brackets in the formula  TODAY()), 1))
Why / when do you use a double closed ()) after Today and then a ,1 at the end?

Starting from the inside, the purple () are part of the TODAY function. TODAY() and TODAY(0) both mean today in UTC time. TODAY(1) means yesterday, TODAY(3) is three days after today, and so on.
The green parentheses enclose the COLLECT function.
The red parentheses enclose the INDEX function. The ,1 at the end of the INDEX function is the row index, meaning give me the first row you find that meets the conditions. So it comes after the embedded COLLECT is closed off, but before closing off the INDEX.
And of course the yellow parentheses enclose the COUNTM function, telling it to use COUNTM on the cell that's returned by the INDEX/COLLECT.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Thanks for the lesson, awesome!!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.8K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!