Incorporating OR within COUNTIFS with Checkboxes
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)))
Best Answers
-
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"))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
That was the ticket, Jeff!!!!!!! Thank you so much for sticking with me😃
Answers
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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.
-
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"))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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.
-
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"))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
That was the ticket, Jeff!!!!!!! Thank you so much for sticking with me😃
-
Third time's the charm. Happy I could help!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
