nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Welcome!

Need help with "IF" formula

Vivien Chong

I am unable to get the formula right for the following scenario:

Column 1: Yes / No

Column 2: Yes / No

If Column 1 and/or Column 2 is blank, then "Not Completed"

If Column 1 and Column 2 is Yes, then "Completed"

If Column 1 is Yes, Column 2 is No, then "In Progress"

If Column 1 is No, Column 2 is Yes, then "KIV"

=IF(ISBLANK([Column 1]1), "Not Completed", IF(ISBLANK([Column 2]1), "Not Completed", IF([Column 1]1 = "Yes", IF([Column 2]1 = "Yes", "Completed", IF([Column 1]1 = "Yes", IF([Column 2]1 = "No", "In Progress", IF([Column 1]1 = "No", IF([Column 2]1 = "Yes", "KIV"))))))))

But the last part of the formula doesn't work (IF([Column 1]1 = "No", IF([Column 2]1 = "Yes", "KIV"). It just return blank cell.

Please help. Thanks.

Find more posts tagged with

Comments

Frank Falco

Try this version:

=IF(OR(ISBLANK(Column1@row),ISBLANK(Column2@row)),"Not Complete",

IF(Column1@row="Yes",IF(Column2@row="Yes","Complete","In Progress"),

IF(Column2@row="No","KIV","---")))

Assuming there is not other options than Blank, Yes or No

1) Check if either are Blank = Not Complete

2) If not, then check if Col1 is Yes, then if Col2 is Yes = Complete, else must be No = In Progress

3) As Col1 is neither blank nor Yes so must be No, then check if Col2 is No = KIV

else = "---" (unknown state - should not happen)

Use @row instead of row numbers as makes it easier for SS to calculate the code

Vivien Chong

Thanks Frank. Your formula works. Appreciate your assistance.

I wonder why my formula couldn't work completely, though the logic is correct.

Sign in to join the conversation!

Quick Links

Trending discussions

"Fields marked with an asterisk (*) are required." line on every form.
Hi, "Fields marked with an asterisk (*) are required." is the very first line in the body of every form I create. I don't remember seeing it before (unless you missed filling out a required field; then you would see the message). Is there a way to turn it off or change it? None of my forms are in English so the message is…
Best Way to Create a Stacked Bar Chart from multiple columns
I have a sheet that I pull into a report. The report I then want to use to make a stacked bar chart and put into a dashboard if I can. The columns I am trying to make into a stacked chart are: Lead, project and phase. The issue is that there are several leads and I need the stacked bar chart to show in different colors for…
Dynamic View Issue - Group Sharing
I have a dynamic view setup for a dashboard that has NO restrictions And on the Sharing tab, I have a Group that I created as the additional share access to the view But the individuals in the Group still cannot see the data from the dashboard. Am I missing something? Because when I share the view to them individually…