Incorporating OR within COUNTIFS with Checkboxes

Suzy Holmes

In English, this is what I am looking for: Total number of tasks with any of these names (Manager, IC, Lead, Director, Grand Poobah) that have the checkbox checked

=COUNTIFS([Task Name]:[Task Name], "Manager", Done1:Done53, 1 (OR([Task Name]:[Task Name] = "IC", Done1:Done53, 1, [Task Name]:[Task Name] = "Lead", Done1:Done53, 1, [Task Name]:[Task Name] = "Director", Done1:Done53, 1 , [Task Name]:[Task Name] = "Grand Poobah ", Done1:Done53, 1)))

Jeff Reisman

https://community.smartsheet.com/discussion/comment/319819#Comment_319819

Might have to take the equal signs out of the criteria. I'm never quite sure when they're needed, LOL. I think you only really need operators in COUNTIFS when you're evaluating numbers. Replace with commas and you should be good.

=COUNTIFS(Done:Done, 1, [Task Name]:[Task Name], OR([Task Name]:[Task Name], "IC", [Task Name]:[Task Name], "Manager", [Task Name]:[Task Name], "Lead", [Task Name]:[Task Name], "Director", [Task Name]:[Task Name], "Grand Poobah"))

Suzy Holmes

That was the ticket, Jeff!!!!!!! Thank you so much for sticking with me😃

Jeff Reisman

You'll want to give your COUNTIFS a Range1, (Done1:Done53) and Criteria1, the 1 which indicates the cell is checked. Then specify your Range2 ([Task Name]:[Task Name]) followed by Criteria2, in this case an OR statement that includes all the possible values for Task Name that you want it to consider.

=COUNTIFS(Done1:Done53, 1, [Task Name]:[Task Name], OR([Task Name]:[Task Name] = "IC", [Task Name]:[Task Name] = "Manager", [Task Name]:[Task Name] = "Lead", [Task Name]:[Task Name] = "Director", [Task Name]:[Task Name] = "Grand Poobah"))

You may run into issues since you are specifying a specific range of the Done column, rather than the entire column. Probably #INCORRECT ARGUMENT SET. Any reason you are only looking at Done1:Done53? Are there more than 53 rows? Make sure your range sizes match.

Suzy Holmes

Thank you, Jeff! The only reason I went for a small range was for troubleshooting purposes. I will use the entire column. I have made the changes you suggested and now am getting #INVALID OPERATION.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/319819#Comment_319819

Might have to take the equal signs out of the criteria. I'm never quite sure when they're needed, LOL. I think you only really need operators in COUNTIFS when you're evaluating numbers. Replace with commas and you should be good.

=COUNTIFS(Done:Done, 1, [Task Name]:[Task Name], OR([Task Name]:[Task Name], "IC", [Task Name]:[Task Name], "Manager", [Task Name]:[Task Name], "Lead", [Task Name]:[Task Name], "Director", [Task Name]:[Task Name], "Grand Poobah"))

Suzy Holmes

When I replace the = with , the error is #INVALID DATATYPE. Sorry to keep asking, but I just don't know how to further troubleshoot this.

Jeff Reisman

Hmmm...

#INVALID DATA TYPE

Cause

The formula contains or references an incompatible data type, such as =INT("Hello")

Resolution

Make sure the formula references the correct data type.

Maybe it doesn't like the repeated range references. Let's try this:

=COUNTIFS(Done:Done, 1, [Task Name]:[Task Name], OR(@cell = "IC", @cell = "Manager", @cell = "Lead", @cell = "Director", @cell = "Grand Poobah"))

Suzy Holmes

That was the ticket, Jeff!!!!!!! Thank you so much for sticking with me😃

Jeff Reisman

https://community.smartsheet.com/discussion/comment/319851#Comment_319851

Third time's the charm. Happy I could help!