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

Syed Muhafzal

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

Bassam Khalil

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

Andrée Starå

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!

Bassam Khalil

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

Girish.E103301

Thanks its working for me

Andrée Starå

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!

Syed Muhafzal

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

@Syed Muhafzal 

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