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

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

AFlint

Hi @Kelly Moore

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.