Need help with "IF" formula
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.
Comments
-
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
✅Did my post help 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!
-
Thanks Frank. Your formula works. Appreciate your assistance.
I wonder why my formula couldn't work completely, though the logic is correct.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives