How to create a formula where if multiple criteria is met, a checkbox is marked?


I am pretty new to using smartsheet formulas, so bear with me! I am trying to create a formula where if multiple cells contain certain text, a checkbox in another cell will be checked off. I want the checkbox to only be marked if multiple criteria are exactly met. If all criteria is not met, I would like it to remain unchecked. I will copy below a screenshot of what I am working with.

Basically, we have students who complete several tasks (comps) for segments. On the image above, segment 1 consists of the columns in the yellow highlighted box (Incomplete Comps-Climbing 1, Incomplete Comps-Distribution 1, Incomplete Comps-Underground, and Incomplete Comps-Equipment 1). Currently, we have it set up where if a student has not completed tasks, the task title is listed in the respective cell. If a student does not have any titles listed and only commas, then he is complete.

For example: The student in the white row is fully complete, so I am wanting the first column, Segment 1 COMPLETE to be checked. The second student in grey is not complete, so I want the checkbox to NOT be marked.

Is this something that is possible within smartsheets? I have tried the following formulas with no luck.

=IF([Incomplete Comps - Climbing 1]@row,", , , , , ,",IF(AND([Incomplete Comps- Distribution 1]@row,",,,,,,")(AND([Incomplete Comps- - Underground]@row,",,,,,,,,,,")(AND([Incomplete Comps- Equipment 1]@row,",,,")=true

=IF([Incomplete Comps - Climbing 1]@row = ", , , , , ," + [Incomplete Comps- Distribution 1]@row = ",,,,,," + [Incomplete Comps- - Underground]@row = ",,,,,,,,,," + [Incomplete Comps- Equipment 1]@row = ",,,") = true

If that sort of thing is possible, I would like to add this to each other segment completion checkbox for their resptive comps as well, and also add the prerequisite that the segment 1 checkbox must be checked as well. Would this be something that smartsheet can do?

Thank you so much for your help! I apologize if this message was a bit confusing- I am still learning.


  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @Rebecca D

    =IF(AND([Incomplete Comps - Climbing 1]@row=", , , , , ,", [Incomplete Comps- Distribution 1]@row=",,,,,,", [Incomplete Comps- - Underground]@row=",,,,,,,,,,", [Incomplete Comps- Equipment 1]@row=",,,"), true)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!