Formula for checked fields

Hello,

I have a sheet that is tracking tasks and I've made it so that there are 10 contact list columns followed by 10 checkbox columns. Each contact list is paired with a checkbox to indicate if an item is done like in the image below:


I want a final column to indicate if every assigned checkbox has been filled out, without any unassigned columns interfering with the outcome (for example if there are only 2 people assigned). The final column will be used as a conditional formatting indicator for our calendar, so a single "yes" response would suffice if all assignees have checked their box. How would you recommend building a formula like this?

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try something like this:

    =IF(COUNTIFS([Done? (1)]@row:[Done? (10)]@row, @cell = 1) = COUNTIFS([Assigned (1)]@row:[Assigned (10)]@row, AND(@cell <> 1, @cell <> "")), "Yes")


    Basically we count across the Done columns how many boxes are checked (1) then compare it to the count of cells from the first Assigned to the last Assigned (not checked and not blank). If those two counts match, then everything that should be checked is checked.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!