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
Check out the Formula Handbook template!