Help w/automatic Status formula
Hi team - I have a Status column that includes:
- Not Started
- In Progress
- Complete
I created this formula that appears to work other than when all required fields have information, it does not go to Complete:
=IF(COUNTIF([1. Aware of XXX vulnerability?]@row, <>"") + COUNTIF([2. Does org use XXX XXX?]@row, <>"") + COUNTIF([3a. Action steps implemented?]@row, <>"") + COUNTIF([3a1. Describe steps]@row, <>"") + COUNTIF([3b. Networked accessed?]@row, <>"") + COUNTIF([3b1. Verifications]@row, <>"") + COUNTIF([3b2. Plans to verify]@row, <>"") = 0, "Not Started", "In Progress")
What am I missing?
Best Answers
-
Here is my suggestion...
=IF(COUNTIFS([1. Aware of Kaseya vulnerability?]@row:[3b2. Plans to verify]@row, @cell = "") = 0, "Complete", IF(COUNTIFS([1. Aware of Kaseya vulnerability?]@row:[3b2. Plans to verify]@row, @cell <> "") = 0, "Not Started", "In Progress"))
-
We would use another IF statement at the beginning of the formula so that it essentially "overrides" the other portions.
=IF([Cyber Team Verified Acceptance]@row = 1, "Verified", IF(COUNTIFS([1. Aware of Kaseya vulnerability?]@row:[3b2. Plans to verify]@row, @cell = "") = 0, "Complete", IF(COUNTIFS([1. Aware of Kaseya vulnerability?]@row:[3b2. Plans to verify]@row, @cell <> "") = 0, "Not Started", "In Progress")))
Answers
-
In looking at the formula, i can see 2 states defined - "Not Started" and "In Progress". "Completed" is not defined in the formula.
-
@cr1616 Any idea how to have 3 states defined? If all fields are blanks (= 0) Not Started; if <> 7 then In Progress; if = 7 then Complete?
-
Team - still needing help.
I tried the following nested IF formula and it appears to work UNTIL all of the necessary fields (5, I reduced the number of columns requiring info) have data in them - then I get an INVALID ARGUMENT error (I created a Status 2 column to test):
=IF(COUNTIF([1. Aware of Kaseya vulnerability?]@row, <>"") + COUNTIF([2. Does org use Kaseya VSA?]@row, <>"") + COUNTIF([3a. Action steps implemented?]@row, <>"") + COUNTIF([3a1. Describe steps]@row, <>"") + COUNTIF([3b. Was your org impacted by this event?]@row, <>"") + COUNTIF([3b1. Verifications]@row, <>"") + COUNTIF([3b2. Plans to verify]@row, <>"") = 0, "Not Started", IF(COUNTIF([1. Aware of Kaseya vulnerability?]@row, <>"") + COUNTIF([2. Does org use Kaseya VSA?]@row, <>"") + COUNTIF([3a. Action steps implemented?]@row, <>"") + COUNTIF([3a1. Describe steps]@row, <>"") + COUNTIF([3b. Was your org impacted by this event?]@row, <>"") + COUNTIF([3b1. Verifications]@row, <>"") + COUNTIF([3b2. Plans to verify]@row, <>""), <=5, "In Progress", "Complete"))
What am I still missing?
-
Here is my suggestion...
=IF(COUNTIFS([1. Aware of Kaseya vulnerability?]@row:[3b2. Plans to verify]@row, @cell = "") = 0, "Complete", IF(COUNTIFS([1. Aware of Kaseya vulnerability?]@row:[3b2. Plans to verify]@row, @cell <> "") = 0, "Not Started", "In Progress"))
-
Genius @Paul Newcome !!!
That formula was money!!!!
-
@Paul Newcome, now how about another variable? :-)
How would I add a nested function to this formula to actually change Status to Verified if the Cyber Team Verified Acceptance checkbox is checked?
Status drop down options are:
- Complete
- In Progress
- Not Started
- Verified
-
We would use another IF statement at the beginning of the formula so that it essentially "overrides" the other portions.
=IF([Cyber Team Verified Acceptance]@row = 1, "Verified", IF(COUNTIFS([1. Aware of Kaseya vulnerability?]@row:[3b2. Plans to verify]@row, @cell = "") = 0, "Complete", IF(COUNTIFS([1. Aware of Kaseya vulnerability?]@row:[3b2. Plans to verify]@row, @cell <> "") = 0, "Not Started", "In Progress")))
-
Once again @Paul Newcome , you come through like a champ!!!
Worked like a charm!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 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!