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 teammember 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 14 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

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

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

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.

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 That worked nicely thanks very much. Logic wasn't flowing clearly when I was looking at it. Cheers!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!