Multiple If statements

Hi, hoping someone will be able to help with an issue I am having with an IF formula.

I am attempting to build an IF formula from check boxes, however it isn't as simple as multiple IF statements returning their own responses.

What I am trying to achieve is a status update column, based off of the checkboxes. And therefore as the process moves onto the next step, the previous step (tick in a checkbox column) will need to have been completed to return the correct status update.

For example:

Site Visit Complete is checked - return "Visit Complete" in the Status column

Site Visit Complete AND Write up Complete are checked - return "Write Up Complete"

Site Visit Complete AND Write up Complete AND Admin QC Complete are checked - return "Admin QC Complete"

Site Visit Complete AND Write up Complete AND Admin QC Complete AND Tech Review Complete are checked - return "Tech QC Complete"

Site Visit Complete AND Write up Complete AND Admin QC Complete AND Tech Review Complete AND Issued to the Client are checked - return "Issued to Client"

I also need to add in an On Hold and Removed From Scope check box to the statement - I assume these would go at the start and they wouldn't need to be connected to any of the other IF statements.

What I have so far:

=IF([On Hold]@row = 1, "On Hold", IF([Removed from Scope]@row = 1, "Removed from Scope")


If anyone can offer some guidance that would be great.


Thank you, Alicia.

Tags:

Best Answer

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    Answer ✓


    =IF([On Hold]@row = 1, "On Hold", IF([Removed from Scope]@row = 1, "Removed from Scope", IF(AND([Site Visit Complete]@row = 1, [Write Up Complete]@row = 1, [Admin QC Complete]@row = 1, [Tech Review Complete]@row = 1, [Issued to the Client]@row = 1), "Issued to Client", IF(AND([Site Visit Complete]@row = 1, [Write Up Complete]@row = 1, [Admin QC Complete]@row = 1, [Tech Review Complete]@row = 1), "Tech Review Complete", IF(AND([Site Visit Complete]@row = 1, [Write Up Complete]@row = 1, [Admin QC Complete]@row = 1), "Admin QC Complete", IF(AND([Site Visit Complete]@row = 1, [Write Up Complete]@row = 1), "Write Up Complete", IF([Site Visit Complete]@row = 1, "Site Visit Complete", "")))))))

Answers

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    Answer ✓


    =IF([On Hold]@row = 1, "On Hold", IF([Removed from Scope]@row = 1, "Removed from Scope", IF(AND([Site Visit Complete]@row = 1, [Write Up Complete]@row = 1, [Admin QC Complete]@row = 1, [Tech Review Complete]@row = 1, [Issued to the Client]@row = 1), "Issued to Client", IF(AND([Site Visit Complete]@row = 1, [Write Up Complete]@row = 1, [Admin QC Complete]@row = 1, [Tech Review Complete]@row = 1), "Tech Review Complete", IF(AND([Site Visit Complete]@row = 1, [Write Up Complete]@row = 1, [Admin QC Complete]@row = 1), "Admin QC Complete", IF(AND([Site Visit Complete]@row = 1, [Write Up Complete]@row = 1), "Write Up Complete", IF([Site Visit Complete]@row = 1, "Site Visit Complete", "")))))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!