# Count if today and multiple criteria

Options
✭✭✭✭

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!

Tags:

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭
edited 03/16/23
Options

Hi Jeff, that worked, thanks!!!!

• ✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭
Options

Thanks for the lesson, awesome!!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!