Status formula - IF function
Hi,
I am struggling to write a formula with the below parameters:
If setup status and monitoring status is not started, then program status = Not started
If setup status and monitoring status is complete, then program status = Complete
If setup status or monitoring status is complete and setup status or monitoring status is not started or in progress, then program status = In progress
If setup status or monitoring status is in progress or not started, then program status = In progress
I tried to create workflows but they didn't work correctly. Also note, the setup status and monitoring status is linked in from another sheet. The screenshot below shows the program status that I entered in manually.
Could someone please help to write a formula for me?
Thanks, Valerie
Best Answer
-
Hey @VSchilke
The side-by-side arrangement of your sheet allows the use of COUNTIFS instead of a complicated Nested IF statement.
=IF(AND([Setup Status]@row <> "", [Monitoring Status]@row <> ""), IF(AND([Setup Status]@row = "Not Started", [Monitoring Status]@row = "Not Started"), "Not Started", IF(AND([Setup Status]@row = "Complete", [Monitoring Status]@row = "Complete"), "Complete", IF(AND(COUNTIFS([Setup Status]@row:[Monitoring Status]@row, "Complete") = 1, OR(COUNTIFS([Setup Status]@row:[Monitoring Status]@row, "Not Started") = 1, COUNTIFS([Setup Status]@row:[Monitoring Status]@row, "In Progress") = 1)), "In Progress", IF(OR(COUNTIFS([Setup Status]@row:[Monitoring Status]@row, "Not Started") >= 0, COUNTIFS([Setup Status]@row:[Monitoring Status]@row, "In Progress") >= 0), "In Progress")))))
Will this work for you?
Kelly
Answers
-
Hey @VSchilke
The side-by-side arrangement of your sheet allows the use of COUNTIFS instead of a complicated Nested IF statement.
=IF(AND([Setup Status]@row <> "", [Monitoring Status]@row <> ""), IF(AND([Setup Status]@row = "Not Started", [Monitoring Status]@row = "Not Started"), "Not Started", IF(AND([Setup Status]@row = "Complete", [Monitoring Status]@row = "Complete"), "Complete", IF(AND(COUNTIFS([Setup Status]@row:[Monitoring Status]@row, "Complete") = 1, OR(COUNTIFS([Setup Status]@row:[Monitoring Status]@row, "Not Started") = 1, COUNTIFS([Setup Status]@row:[Monitoring Status]@row, "In Progress") = 1)), "In Progress", IF(OR(COUNTIFS([Setup Status]@row:[Monitoring Status]@row, "Not Started") >= 0, COUNTIFS([Setup Status]@row:[Monitoring Status]@row, "In Progress") >= 0), "In Progress")))))
Will this work for you?
Kelly
-
Hi Kelly!
Thank you very much for your help. It worked perfectly!! 😃
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!