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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!