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

Find more posts tagged with

Formulas

Accepted answers

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!

All comments

Bassam Khalil

Hi @Syed Muhafzal

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

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.