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 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.
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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!