SOLVED - COUNTIFS Problem

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

Hi,

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)))

Tags:
· Share on FacebookShare on Twitter

Comments

  • Shaine Greenwood
    Shaine Greenwood Employee

    Hello,

    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))

    · Share on FacebookShare on Twitter
  • Outofmydepth
    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.

     

     

    · Share on FacebookShare on Twitter
  • Outofmydepth
    Outofmydepth ✭✭

    Solved by adding an extra =. 

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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!