Outofmydepth ✭✭
edited 12/09/19 in Formulas and Functions


I'm trying to return a status within a range when the finish date is within 7 days, I've tried a few alternatives with the latest below (using @cell) which I know isn't correct but I'm hoping I'm close. Any advice would be greatly appreciated.

=COUNTIFS({RangeStatus}, [Not Started]1, {RangeFinish}, @cell <=TODAY(), @cell > TODAY(7)))



  • Shaine Greenwood


    I noticed a few things here:

    First off, you don't need @cell, you can type your criteria directly after the comma: =COUNTIFS({RangeStatus}, [Not Started]1, {RangeFinish}, <=TODAY())

    COUNTIFS will only count cells that meet all criteria. I don't believe that a date can both be less than or equal to the current date and also greater than the current date plus 7 days.

    If you're wanting to add together the count of dates that meet the [Not Started]1 criteria and only ONE of the two date criteria, try adding COUNTIFS statements together:

    =COUNTIFS({RangeStatus}, [Not Started]1, {RangeFinish}, <=TODAY()) + COUNTIFS({RangeStatus}, [Not Started]1, {RangeFinish}, >TODAY(7))

  • Outofmydepth

    Hi Shaine,

    Thanks for your response, my original formula was

    =COUNTIFS({RangeFinish}, <=TODAY(7), {RangeStatus}, $[Not Started]$1)

    which I though would work but it didn't. I tried the @cell as an option but that obviously wouldn't work.

    Smartsheet support have actually come back to me with this example formula:

     =COUNTIFS(Enddate:Enddate, "not started", DueTime:DueTime, >TODAY(), DueTime:DueTime, <=TODAY(7))

    but alas that doesn't return the correct value - I have 5 rows set at "Not Started" that have Finish dates due within 7 days but the above formula only returned a value of 1.

    I'm baffled.



  • Outofmydepth

    Solved by adding an extra =. 

    =COUNTIFS(Enddate:Enddate, "not started", DueTime:DueTime, >=TODAY(), DueTime:DueTime, <=TODAY(7))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!