Status formula - IF function

VSchilke

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

Kelly Moore

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

Kelly Moore

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

VSchilke

Hi Kelly!

Thank you very much for your help. It worked perfectly!! 😃