Help with formula again
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 noncompliant.
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.
Best Answers

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

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

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

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