Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

How to combine if statements utilizing information in multiple columns

Hello,

I currently have the following formula to automatically have a color coded dot assigned in a column depending on the due date.

=IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row - TODAY() > 5, "", IF([Due Date]@row <= TODAY(), "Red", "Yellow")))

Can you help me add to this same formula an if statement that specifies:

If Status in the row is "Complete", mark at green. If it's not, complete, follow the rest of the rules above.

Best Answer

  • Answer ✓

    The easiest way to implement this would be to ignore the due date logic entirely. I added an IF at the beginning that makes it only run through the code you have if the status is not "Done"

    I tested this on one of my sheets and it worked

    =IF(Status@row = "Done", "Green", IF(ISBLANK(Deadline@row), "", IF(Deadline@row - TODAY() > 5, "", IF(Deadline@row <= TODAY(), "Red", "Yellow"))))


    For you it should look like this:

    =IF(Status@row = "Completed", "Green", IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row - TODAY() > 5, "", IF([Due Date]@row <= TODAY(), "Red", "Yellow"))))

Answers

  • Answer ✓

    The easiest way to implement this would be to ignore the due date logic entirely. I added an IF at the beginning that makes it only run through the code you have if the status is not "Done"

    I tested this on one of my sheets and it worked

    =IF(Status@row = "Done", "Green", IF(ISBLANK(Deadline@row), "", IF(Deadline@row - TODAY() > 5, "", IF(Deadline@row <= TODAY(), "Red", "Yellow"))))


    For you it should look like this:

    =IF(Status@row = "Completed", "Green", IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row - TODAY() > 5, "", IF([Due Date]@row <= TODAY(), "Red", "Yellow"))))

  • ✭✭

    That is extremely helpful and worked!! Thank you so very much!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions