Countifs Calculations Multiple Selections in Cell

Options

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.

• Options

Paul, thank you so much. I've been racking my brain over this one.

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!