Need a box checked if one of two criteria is met

TracyM

Hi! I have a column and I need the box to check if a student's disability is Intellectual Disability or Multiple Disabilities. I have the formula correct if it's just one:

=IF([Primary Disability]@row = "Multiple Disabilities", 1, IF([Secondary Disability]@row = "Multiple Disabilities", 1, IF([Tertiary Disability]@row = "Multiple Disabilities", 1, IF([Quaternary Disability]@row = "Multiple Disabilities", 1, 0))))

But I can't figure out how to get it to work if it's either/or.

thanks in advance for any help you can offer!

Jeff Reisman

@TracyM

You can use CONTAINS to search for a value within a range of cells. If all 4 columns are next to each other starting with Primary on the left and ending with Quaternary on the right, try this:

=IF(OR(CONTAINS("Multiple Disabilities", [Primary Disability]@row:[Quaternary Disability]@row), CONTAINS("Intellectual Disability", [Primary Disability]@row:[Quaternary Disability]@row)), 1, 0)

In English: If the text string "Multiple Disabilities" or the text string "Intellectual Disability" is found anywhere in these four columns on this row, check the box, otherwise, don't check the box.

SmartLew

Assuming you are looking at your Primary Disability Column for the formula;

=if(or([Primary Disability]@row = "Multiple Disabilities",[Primary Disability]@row = "Intellectual Disability"),1)

TracyM

that's the tricky part - if it's in any of the columns it needs to check so Primary, Secondary, Tertiary, or Quaternary

Jeff Reisman

@TracyM

You can use CONTAINS to search for a value within a range of cells. If all 4 columns are next to each other starting with Primary on the left and ending with Quaternary on the right, try this:

=IF(OR(CONTAINS("Multiple Disabilities", [Primary Disability]@row:[Quaternary Disability]@row), CONTAINS("Intellectual Disability", [Primary Disability]@row:[Quaternary Disability]@row)), 1, 0)

In English: If the text string "Multiple Disabilities" or the text string "Intellectual Disability" is found anywhere in these four columns on this row, check the box, otherwise, don't check the box.

SmartLew

https://community.smartsheet.com/discussion/comment/344140#Comment_344140

You can just repeat your original formula with "Intellectual Disability" as the criteria. This then covers all bases.

Jeff's suggestion above could be used for both to condense the formula a little.