Count if today and multiple criteria

Sam Swain

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!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/367593#Comment_367593

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.

Jeff Reisman

@Sam Swain

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.

Sam Swain

Hi Jeff, that worked, thanks!!!!

Sam Swain

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?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/367593#Comment_367593

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.

Sam Swain

https://community.smartsheet.com/discussion/comment/367595#Comment_367595

Thanks for the lesson, awesome!!