COUNTIFS multiple sheets but search if CONTAINS assigned to name


I am trying to count the number of tasks assigned to an employee across multiple sheets and if the task is not marked "complete". This formula works only if the employee is the only person assigned to a task. I want it to include any their name appears with multiple employees in the assigned to cell. (So if the cell "Contains" their name). I've tried multiple versions of adding CONTAINS(Primary 8) without luck

=COUNTIFS({Assigned To}, Primary8, {State}, <>"Complete") + COUNTIFS({Assigned To Range 2}, Primary8, {State Range 2}, <>"Complete") + COUNTIFS({Assigned To Range 3}, Primary8, {State Range 3}, <>"Complete") + COUNTIFS({Assigned To Range 4}, Primary8, {State Range 4}, +COUNTIFS({Assigned To Range 5}, Primary8, {State Range 5}, <>"Complete"))

Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!