Help Calculating Variable Status
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 16 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
Best Answer

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
Answers

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

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.
Help Article Resources
Categories
Check out the Formula Handbook template!