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
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!