Help Calculating Variable Status

AFlint

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

Kelly Moore

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