Formula - Add "Blank" as a value in IF formula

Hi Smarties,

I know this is probably an easy one, but I have been cracking my head over the last 2 days so here I am!

I have 4 columns representing Task 1 to Task 4. Each column has a single Drop-Down with Not Yet Started, In Progress and Completed.

On the column Health, I have symbols of Red, Yellow and Green.

Basically, I want to build an IF function, whereby if one of the cells in Task 1 to Task 4 is Not Yet Started, it will turn Red, if any of them In Progress it will turn Yellow otherwise Green (if Completed).

I managed to do this with Contains, see below :

=IF(CONTAINS("Not Yet Started", [Task 1]@row:[Task 4]@row), "Red", IF(CONTAINS("In Progress", [Task 1]@row:[Task 4]@row), "Yellow", "Green"))

However, I need to put Blank as Red as well as you can see it is showing Green when the cells are Blank.

I have tried to use ISBLANK, OR and AND but I am not sure if I have done it right.

Any advice will be appreciated otherwise I wont be able to sleep tonight! 😁

Thanks

Syed

Tags:

Best Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @Syed Muhafzal 

    Hope you are fine, please find in the following the formula as per your description.

    =IF(OR([Task 1]@row = "Not Yet Started", [Task 2]@row = "Not Yet Started", [Task 3]@row = "Not Yet Started", [Task 4]@row = "Not Yet Started", ISBLANK([Task 1]@row), ISBLANK([Task 2]@row), ISBLANK([Task 3]@row), ISBLANK([Task 4]@row)), "Red", IF(CONTAINS("In Progress", [Task 1]@row:[Task 4]@row), "Yellow", "Green"))

    the following screenshot shows the result


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 04/25/21 Answer ✓

    Hi @Syed Muhafzal

    I hope you're well and safe!

    To add to Bassam's excellent advice/answer.

    Here's another option.

    =IF(COUNTIF([Task 1]@row:[Task 4]@row; "") > 0; "Red"; IF(CONTAINS("Not Yet Started"; [Task 1]@row:[Task 4]@row); "Red"; IF(CONTAINS("In Progress"; [Task 1]@row:[Task 4]@row); "Yellow"; "Green")))

    Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @Syed Muhafzal 

    Hope you are fine, please find in the following the formula as per your description.

    =IF(OR([Task 1]@row = "Not Yet Started", [Task 2]@row = "Not Yet Started", [Task 3]@row = "Not Yet Started", [Task 4]@row = "Not Yet Started", ISBLANK([Task 1]@row), ISBLANK([Task 2]@row), ISBLANK([Task 3]@row), ISBLANK([Task 4]@row)), "Red", IF(CONTAINS("In Progress", [Task 1]@row:[Task 4]@row), "Yellow", "Green"))

    the following screenshot shows the result


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Thanks its working for me

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 04/25/21 Answer ✓

    Hi @Syed Muhafzal

    I hope you're well and safe!

    To add to Bassam's excellent advice/answer.

    Here's another option.

    =IF(COUNTIF([Task 1]@row:[Task 4]@row; "") > 0; "Red"; IF(CONTAINS("Not Yet Started"; [Task 1]@row:[Task 4]@row); "Red"; IF(CONTAINS("In Progress"; [Task 1]@row:[Task 4]@row); "Yellow"; "Green")))

    Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Syed Muhafzal
    Syed Muhafzal ✭✭✭✭✭
    edited 04/25/21

    Hi @Andrée Starå @Bassam.M Khalil

    You guys rock! Yup these two strings work and I can now see what I did wrong (or didnt do).

    Although @Andrée Starå do you mind letting me know, why did you put ">0" in the formula? I believe this was the missing piece in one of my attempts earlier :)

    Thank you both and I hope you are doing well too!

    Syed

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Syed Muhafzal 

    Thanks for the kind words and we are happy to help you at any time

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!