Countifs Calculations Multiple Selections in Cell

Adam Boyle

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.

Paul Newcome

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 Newcome

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)

Adam Boyle

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

Paul Newcome

Happy to help! 👍️