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
- 66.7K Get Help
- 436 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
- Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
- I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …