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 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
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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!