Help with formula again

Options
jgneely72151
jgneely72151 ✭✭✭✭✭
edited 07/05/24 in Formulas and Functions

Hello,

Sorry that I have been away but the last discussion I had regarding a formula revison still does not work. I've simplified my field names below and provided an example of what I would like displayed for the compliance status.

Scenario:

If all 3 columns have check marks and the flag is blank, then compliant.

If 0 or 1 out of 3 have check marks and flag is blank, then non-compliant.

If 2 out of 3 are checked and flag is blank, then partially compliant.

Lastly, if any of them have a flag of "revoked" or "deferred", then cancelled.

Can anyone write this formula for me? It is quite challenging.

Tags:

Best Answers

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    edited 07/05/24 Answer ✓
    Options

    Hi @jgneely72151, you can try this formula

    =IF(Flag@row = "", IF(COUNTIF([Student Health]@row:Advantage@row, 1) = 3, "Compliant", 
    IF(COUNTIF([Student Health]@row:Advantage@row, 1) = 2, "Partially Compliant", "Not Compliant")), "Cancelled")
    

    If flag is blank, it will count how many of those checkboxes are selected and meet the criteria you wish to have.

    Sincerely,

    Jacob Stey

  • jgneely72151
    jgneely72151 ✭✭✭✭✭
    Answer ✓
    Options

    OMG!!! It worked. I was trying to use an if/or and if/and statement and kept running into errors. Perfect! I appreciate your help so much!

Answers

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    edited 07/05/24 Answer ✓
    Options

    Hi @jgneely72151, you can try this formula

    =IF(Flag@row = "", IF(COUNTIF([Student Health]@row:Advantage@row, 1) = 3, "Compliant", 
    IF(COUNTIF([Student Health]@row:Advantage@row, 1) = 2, "Partially Compliant", "Not Compliant")), "Cancelled")
    

    If flag is blank, it will count how many of those checkboxes are selected and meet the criteria you wish to have.

    Sincerely,

    Jacob Stey

  • jgneely72151
    jgneely72151 ✭✭✭✭✭
    Answer ✓
    Options

    OMG!!! It worked. I was trying to use an if/or and if/and statement and kept running into errors. Perfect! I appreciate your help so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!