=IF([End Date]@row,>=TODAY,"Yes", "no")

Trying to tabulate in process Items.. I want it to toggle YES if the end date is past today and No if the end date has not been reached--- from there I want to sum the "Yes" values

I have to break this up by quarter so also was going to start with each item by date then add an additional for Quarter so... If Q1 and Date is after today "yes" if Q1 and before today "no".

Thank you in advance!



  • AnalyticOwl
    AnalyticOwl ✭✭✭
    edited 12/14/22

    Hi Sam,

    If you change your initial IF formula to:

    =IF([End Date]@row >= TODAY(), "Yes", "no") will do what you want, and then count them you can do:

    =COUNTIF([name of column]:[name of column], "Yes") using the name of the column as the name where you put the formula above.

    About the quarters you can set up an additional column with a formula like:

    =IF(ISDATE([End date]@row), IF(MONTH([End date]@row) <= 3, "Q1", IF(MONTH([End date]@row) <= 6, "Q2", IF(MONTH([End date]@row) <= 9, "Q3", "Q4"))))

    You can do a combine COUNTIFS including both conditions but I am not sure what exactly you want to count on that side but hope this could give you a good start!

    Good vibes


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    You can use the AND function within IF to designate multiple criteria that must be true. The syntax is

    IF(AND(logical expression 1, logical expression 2, ...), value if true, value if false)

    In this case, I'm nesting a second IF because presumably you don't want to set the value for anything not in Q1.

    =IF(AND(Quarter@row = "Q1", [End Date]@row >= TODAY()), "Yes", IF(AND(Quarter@row = "Q1", [End Date]@row < TODAY()), "No"))

    To count the Yes values, use COUNTIFS. You don't need AND here because it is included in the COUNTIFS function.

    The syntax for COUNTIFS is:

    COUNTIFS(range 1, criteria 1, range 2, criteria 2, ...)

    =COUNTIFS(Quarter:Quarter, "Q1", Status:Status, "Yes")


    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!