Check a box when multiple criteria are met

Randy Van Winkle

Hello, I searched for similar, but did not quite find my answer. I have a sheet with multiple column types. One with an linked cell for project completion on another sheet, several check boxes, RYG buttons, dates, text and numbers. Would like to create a formula column the automatically checks the box "Primary Complete" when all meet the criteria. Project Completion must meet 100%, check boxes must be checked,, criteria 4 reads 'complete", Any Date in criteria 5, criteria 6 is green, any number in criteria 7, then Primary Complete gets checked. I tried to draft a formula but quickly ran aground. Any help appreciated

Paul Newcome

This should work:

=IF(AND([Project Completion]@row = 1, [Criteria 1]@row = 1, [Criteria 2]@row = 1, [Criteria 3]@row = 1, [Criteria 4]@row = "complete", [Criteria 5]@row <> "", [Cirteria 6]@row = "Green", [Cirteria 7]@row <> ""), 1)

Paul Newcome

That would look like this:

=IF(AND([Project Completion]@row = 1, [Criteria 1]@row = 1, [Criteria 2]@row = 1, [Criteria 3]@row = 1, OR([Criteria 4]@row = "complete", [Criteria 4]@row = "N/A"), [Criteria 5]@row <> "", [Cirteria 6]@row = "Green", [Cirteria 7]@row <> ""), 1)

Paul Newcome

This should work:

=IF(AND([Project Completion]@row = 1, [Criteria 1]@row = 1, [Criteria 2]@row = 1, [Criteria 3]@row = 1, [Criteria 4]@row = "complete", [Criteria 5]@row <> "", [Cirteria 6]@row = "Green", [Cirteria 7]@row <> ""), 1)

Randy Van Winkle

Worked out great! thanks. How would I modify to include a second response for example if Criteria 4 was marked N/A and I wanted to accept either "complete" or "NA" as passing the query?

Paul Newcome

That would look like this:

=IF(AND([Project Completion]@row = 1, [Criteria 1]@row = 1, [Criteria 2]@row = 1, [Criteria 3]@row = 1, OR([Criteria 4]@row = "complete", [Criteria 4]@row = "N/A"), [Criteria 5]@row <> "", [Cirteria 6]@row = "Green", [Cirteria 7]@row <> ""), 1)

Randy Van Winkle

Awesome, thanks so much for your help

Paul Newcome

Happy to help. 👍️