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?

Find more posts tagged with

Accepted answers

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

All comments

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...

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.

jmo

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

Worked like a charm!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.