Setting checkbox as true based on conditions of multiple columns

Mallory N

Hi! I am trying to set a formula that will check a checkbox based on conditions of multiple columns.

This is the formula that I am trying to use:

=IF((COUNTIFS([PMO: Contract requirements review]24:[PMO: Is customer source inspection required?]24, "complete")+countifs([PMO: Contract requirements review]24:[PMO: Is customer source inspection required?]24, "N/A"))<countA([PMO: Contract requirements review]24:[PMO: Is customer source inspection required?]24)+countblank([PMO: Contract requirements review]24:[PMO: Is customer source inspection required?]24),0,1)

I would like to for the checkbox to be checked if all columns read "complete" or "N/A".

Any assistance would be greatly appreciated 😀

Paul Newcome

Try this instead. Count how many are blank, and if that number equals zero, check the box.

=IF(COUNTIFS([First Column]@row:[Last Column]@row, @cell = "") = 0, 1)

Mallory N

All of the columns will have data in them. I am looking to only check the checkbox if the text contained in the columns is "complete" or "N/A".

=(COUNTIFS(B3:E3,"COMPLETE")+COUNTIFS(B3:E3,"N/A"))

This is a formula that works in excel to produce the results I need, but I am having a hard time translating it to Smartsheet.

Paul Newcome

Give this a shot to check the box

=IF(COUNTIFS([First Column]@row:[Last Column]@row, AND(@cell <> "Complete", @cell <> "N/A", @cell <> "")) = 0, 1)

Mallory N

Success! Kind of…

I used this formula and it is giving the desired result, mostly, but I would also like for it to not check the box if there are any blanks.. is that possible?

=IF(COUNTIFS([PMO: Contract requirements review]@row:[PMO: Create checklist for software deliverables]@row, AND(@cell <> "complete, @cell <> "N/A")) = 0, 1, 0)

Paul Newcome

Yes. Mine would do that.

Mallory N

This is how I have it input and it is still checking the checkbox where there are blanks.

Paul Newcome

Sorry. Flipped one of my operators. Try this:

=IF(COUNTIFS([First Column]@row:[Last Column]@row, AND(@cell <> "Complete", @cell <> "N/A", @cell = "")) = 0, 1)