Help w/automatic Status formula

jmo
jmo ✭✭✭✭✭✭
edited 07/07/21 in Formulas and Functions

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

Answers

  • Chuck Rodgers
    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
    jmo ✭✭✭✭✭✭
    edited 07/07/21

    @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
    jmo ✭✭✭✭✭✭
    edited 07/08/21

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    jmo ✭✭✭✭✭✭

    Genius @Paul Newcome !!!

    That formula was money!!!!

  • jmo
    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    jmo ✭✭✭✭✭✭

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

    Worked like a charm!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!