Help Calculating Variable Status

Options
✭✭✭✭

I have a checklist with 7 items. Each item is a yes/no drop down, and they can be completed in any order. I need to automate (via formula) one of 3 statuses based on the following conditions:

1) No response (yes or no) in any of the 7 columns - all blank. Status: Not Started

2) Responses (yes or no) in 1-6 of the columns, any order or combination - 1 or more blanks. Status: In Progress

3) Responses in all columns (yes or no) - no blanks. Status: Complete

Is there a function or formula that can help me assign the status based on those criteria?

Thank you

• ✭✭✭✭✭✭
Options

Hey @AFlint

I will presume that your 7 columns are contiguously placed on your sheet - in other words, all side by side together.

=IF(COUNT([Column1]@row:[Coulumn7]@row)=0, "Not Started", IF(COUNT([Column1]@row:[Coulumn7]@row)=7, "Complete", IF(AND(COUNT([Column1]@row:[Column6]@row)>0, COUNT([Column1]@row:[Column6]@row)<6), "In Progress")))

For the In Progress - what is the state of Column7? Are we missing an additional AND criteria?

Does this formula work for you?

Kelly

• ✭✭✭✭✭✭
Options

Hey @AFlint

I will presume that your 7 columns are contiguously placed on your sheet - in other words, all side by side together.

=IF(COUNT([Column1]@row:[Coulumn7]@row)=0, "Not Started", IF(COUNT([Column1]@row:[Coulumn7]@row)=7, "Complete", IF(AND(COUNT([Column1]@row:[Column6]@row)>0, COUNT([Column1]@row:[Column6]@row)<6), "In Progress")))

For the In Progress - what is the state of Column7? Are we missing an additional AND criteria?

Does this formula work for you?

Kelly

• ✭✭✭✭
Options

1) Yes, the columns are contiguous in sequence

2) I actually worked out something similar to your post

=IF(COUNTIF([First Column]@row:[Last Column]@row, ="") = 7, "Not Started", IF(COUNTIF([First Column]@row:[Last Column]@row, ="") > 0, "In Progress", "Review Complete"))

As you can see, I focused on the blanks and used the 7 to indicate all blanks, then if anything ">0" is not blank, that means it is started, otherwise it is complete.

Thank you for the help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!