Formula change help

Options

Answers

  • Please help
    Options

    The equation was just if there was 0 dates up to 4 filled in there was a pending word. then if there are all 5 dates filled it in says complete.

    =IF(COUNT([Date Certified and First Class Letters Mail to Pts]@row:[Date Entered into Event Reporting tool]@row) = 0, "Pending", IF(COUNT([Date Certified and First Class Letters Mail to Pts]@row:[Date Entered into Event Reporting tool]@row) = 6, "Complete", "Pending"))

    Now we added a column that says words in between the first and second columns so the equation needs changing. only the first column and then the last 3 are required for the equation to say complete but also the second or thrid column can be filled in sometimes but may never have a date so it needs to not be totally factored in. how do i write htis equation?


  • Ipshita
    Ipshita ✭✭✭✭✭✭
    Options

    @Please help

    When you edit the formula above to factor in the new columns you are adding, you have to add a IF(ISBLANK([Date]@row), "")) towards the very end to complete it where "Date" is all the date columns you need factoring in (which cannot be blank) and for the " " put in the desired value of "Complete" or "Pending"

    Hope this helps.

    Cheers,

    Ipshita

    Ipshita Mukherjee

  • Please help
    Options

    so the third column may never have a date in it so the column can be empty but if the other 4 are filled in then it is complete. what do i do @Ipshita

  • Ipshita
    Ipshita ✭✭✭✭✭✭
    Options

    so write your formula as you would for all the above conditions and at the end, just add another IF statement, wherein - he first part is your new edited formula and then add

    =IF(COUNT([Date Certified and First Class Letters Mail to Pts]@row:[Date Entered into Event Reporting tool]@row) = 0, "Pending", IF(COUNT([Date Certified and First Class Letters Mail to Pts]@row:[Date Entered into Event Reporting tool]@row) = 6, "Complete", "Pending")), IF(ISBLANK([Date in 3rd column]@row), "Complete"))

    Ipshita Mukherjee

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!