Help w/automatic Status formula

jmo

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?

Paul Newcome

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"))

Paul Newcome

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")))

Chuck Rodgers

In looking at the formula, i can see 2 states defined - "Not Started" and "In Progress". "Completed" is not defined in the formula.

jmo

@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?

jmo

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?

Paul Newcome

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"))

jmo

Genius @Paul Newcome !!!

That formula was money!!!!

jmo

@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

Paul Newcome

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")))

jmo

Once again @Paul Newcome , you come through like a champ!!!

Worked like a charm!

Paul Newcome

Happy to help. 👍️