Formula using a checkbox column to get Yes/No

Noella

Greetings,

I'm trying to write a formula that will give me a yes or no in a new column when criteria is meet. For example, if in Services Needed column contains HVAC, Lock/Unlock, Lighting Only AND HVAC Completed, Lock/Unlock Completed, Lighting Only Completed are all checked then it will equal Yes in all services done? column. So far all my formula attempts have been #UNPARSEABLE

=IF(AND(CONTAINS(“Lock/Unlock”, [Services Needed]@row), CONTAINS ("HVAC", [Services Needed]@row), CONTAINS ("Lighting Only", [Services Needed]@row), [HVAC Completed]@row = 1, [Lighting Completed]@row = 1, [Lock/Unlock Complete]@row = 1), "Yes", "No")

Kaveri Vipat

Hi Noella 

I hope you are doing well and safe, 

I have created 2 formulas according to the condition. 

First, the formula is, if the Services Needed column in a single cell contains all these three values HVAC, Lock/Unlock, Lighting Only AND HVAC Completed, Lock/Unlock Completed, Lighting Only Completed are all checked then these formulas will work - 

=IF(AND(CONTAINS("Lock/Unlock", [Services Needed]@row), CONTAINS("HVAC", [Services Needed]@row), CONTAINS("Lighting", [Services Needed]@row), [HVAC Completed]@row = 1, [Lighting Completed]@row = 1, [Lock/Unlock Complete]@row = 1), "Yes", "No") 

The Second formula is, if the Services Needed column in a single cell contains any single values HVAC, Lock/Unlock, Lighting Only AND HVAC Completed, Lock/Unlock Completed, Lighting Only Completed are all checked then these formulas will work - 

=IF(AND(OR(CONTAINS("Lock/Unlock", [Services Needed]@row), CONTAINS("HVAC", [Services Needed]@row), CONTAINS("Lighting", [Services Needed]@row)), AND([HVAC Completed]@row = 1, [Lighting Completed]@row = 1, [Lock/Unlock Complete]@row = 1)), "Yes", "No") 

I hope this is useful to you, please let me know if you need any other help.  

Have a Good Day! 

Noella

The proposed formula does work but it doesn't give the correct result. When I try to add in additional formulas for all the possible conditions/combinations it gives me nothing.

Kaveri Vipat

Hi Noella 

What additional formula are you using for all conditions, can you please share the formula that are you using?  

Have a Good Day!