Countifs Calculations Multiple Selections in Cell
I'm not quite sure how to ask this, but I have cell that has a list of teams you can select from. You can select one team, or have multiple teams selected. If multiple teams are selected then the line item (task) is excluded from the count. I am trying to determine how to modify my calculation to account for multiple selections.
Below is my current formula:
=COUNTIFS({Client Plan - Range 1}, $[Team Name]1, {Client Plan - Range 2}, "COMPLETE", {Client Plan - Range 1}, <>"", {Client Plan - Range 3}, =0, {Client Plan - Range 4}, =0)
This formula works when only one team is selected and will count line item tasks that have been completed by that team. If I select another team that had to complete the same task it will not count it for either team.
I would appreciate any assistance in modifying my formula. I have tried multiple suggestions in reading through the community forum without any luck.
Thank you.
Best Answer
-
Try this...
=COUNTIFS({Client Plan - Range 1}, CONTAINS($[Team Name]1, @cell), {Client Plan - Range 2}, "COMPLETE", {Client Plan - Range 1}, <>"", {Client Plan - Range 3}, =0, {Client Plan - Range 4}, =0)
Answers
-
Try this...
=COUNTIFS({Client Plan - Range 1}, CONTAINS($[Team Name]1, @cell), {Client Plan - Range 2}, "COMPLETE", {Client Plan - Range 1}, <>"", {Client Plan - Range 3}, =0, {Client Plan - Range 4}, =0)
-
Paul, thank you so much. I've been racking my brain over this one.
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!