CountIF Series of Checkboxes, up to last unchecked box

Hello, I have a sheet with a series of 6 check boxes for different tasks. A team-member checks a box when an operation/task is complete. Some tasks can be done concurrently and are not dependent on another, but I would like to indicate the last completed task up until the next blank check box.

Example 1:

Checkboxes 1-4 are complete, I would like a status column to indicate complete through task 4.

Example 2:

Checkboxes 1,2 and 4 are complete, I would like the status to indicate complete through task 2.

My plan was to nest a COUNTIF with an AND / IF statement for each check box, but I can't seem to figure it out. The first example above would count 4 for each task completed but count 2 for example 2. The formula got pretty lengthy so I won't paste if here unless someone needs it.

Linking the counted checkboxes to the status is not an issue, so I really just need the count formula to work properly. Any ideas would be most welcome.

Thanks all!

Best Answer

  • Scott Peters
    Scott Peters ✭✭✭✭✭✭
    Answer ✓

    Hi @estradamee - Is this helpful?

    The formula I used is:

    =IF(COUNTIFS([Task A]@row:[Task F]@row, 1) = 6, "Task F", IF(COUNTIFS([Task A]@row:[Task E]@row, 1) = 5, "Task E", IF(COUNTIFS([Task A]@row:[Task D]@row, 1) = 4, "Task D", IF(COUNTIFS([Task A]@row:[Task C]@row, 1) = 3, "Task C", IF(COUNTIFS([Task A]@row:[Task B]@row, 1) = 2, "Task B", IF([Task A]@row = 1, "Task A", IF(COUNTIFS([Task A]@row:[Task F]@row, 1) = 0, "Not Started", "In Progress")))))))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!