CountIF Series of Checkboxes, up to last unchecked box

estradamee

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!

Scott Peters

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")))))))

Scott Peters

Hello @estradamee - Are all of your checkboxes in the same row (6 unique columns), or is it one checkbox column and six rows?

estradamee

Hi @Scott Peters

It would be @row level, across 6 columns. So the formula could be made into column formula and indicate the status for dozens of parts automatically based on the series of 6 check boxes per row.

Scott Peters

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")))))))

estradamee

@Scott Peters That worked nicely thanks very much. Logic wasn't flowing clearly when I was looking at it. Cheers!