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.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
