# Formula using a checkbox column to get Yes/No

Options
✭✭

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")

Tags:

• ✭✭✭✭✭✭
Options

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!

Thanks,

Kaveri Vipat

Senior Associate - Smartsheet Development, Ignatiuz Software

2023 Core Product Certified

• ✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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!

Thanks,

Kaveri Vipat

Senior Associate - Smartsheet Development, Ignatiuz Software

2023 Core Product Certified