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.

At Risk Flag Count

I have a formula set up to capture when a flag is activated, i.e. when the Status is in the past, the flag is activated (unless the Status is On Hold, Cancelled or Complete).

I also want the flag to be activated when the Status is changed to Unresourced, but can't quite work out that piece of the formula. Please can someone help me add this function to the below formula. Thank you.

=IF(ISBLANK(Status@row), "", IF(ISBLANK(Start@row), "", IF(AND(Finish@row < TODAY(), NOT(Status@row = "On Hold"), NOT(Status@row = "Cancelled"), NOT(Status@row = "Complete")), 1, 0)))

Best Answer

  • ✭✭✭✭✭✭
    Answer ✓

    Hi @Michelle Maas


    Try this:

    =IF(OR(ISBLANK(Status@row),ISBLANK(Start@row)),0,IF(Status@row="Unresourced",1,IF(AND(Finish@row < TODAY(), NOT(Status@row = "On Hold"), NOT(Status@row = "Cancelled"), NOT(Status@row = "Complete")), 1, 0))))


    Let me know if it works!

    Best,

    Heather

Answers

  • ✭✭✭✭✭✭
    Answer ✓

    Hi @Michelle Maas


    Try this:

    =IF(OR(ISBLANK(Status@row),ISBLANK(Start@row)),0,IF(Status@row="Unresourced",1,IF(AND(Finish@row < TODAY(), NOT(Status@row = "On Hold"), NOT(Status@row = "Cancelled"), NOT(Status@row = "Complete")), 1, 0))))


    Let me know if it works!

    Best,

    Heather

  • ✭✭✭✭
    edited 05/17/21

    @Heather D thanks for getting back to me. No it brings up an error that something is not quite right.

    I switched the Unresourced part to the front of the formula and looks like it is working now. Thanks for getting me on the right track. I don't know how to change the response to Yes, but have Voted Up the answer.

    =IF(Status@row = "Unresourced", 1, IF(ISBLANK(Status@row), "", IF(ISBLANK(Start@row), "", IF(AND(Finish@row < TODAY(), NOT(Status@row = "On Hold"), NOT(Status@row = "Cancelled"), NOT(Status@row = "Complete")), 1, 0))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions