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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    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.


    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    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!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!