Help with formula again

jgneely72151

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.

SteyJ

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.

jgneely72151

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!

SteyJ

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.

jgneely72151

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!