COUNTIFS and CONTAINS - Multiple Criteria

I have a formula that I am using across worksheets that is working well. However, was just informed that I will need to account for multiple Project Specialists on one project and cannot get the COUNTIFS & CONTAINS to work for me.

My current formula is:

=COUNTIFS({TAC Sourcing Project Tracker - PROJECT SPECIALIST}, $[Primary Column]@row, {TAC Sourcing Project Tracker - STATUS}, <>"Completed", {TAC Sourcing Project Tracker - STATUS Canceled}, <>"Canceled", {TAC Sourcing Project Tracker - BUSINESS}, "Retail")

I am counting how many "Retail" projects a Project Specialist is working on that are active...so drop down status is not "Completed" and not "Canceled". However, I need to be able to use the CONTAINS formula to count projects where there is more than one Project Specialist in the "Project Tracker - PROJECT SPECIALIST" reference. I want to use the CONTAINS formula on the first part of the formula for the $[Primary Column]@row reference.

The field for Project Specialist is a Contact List but I have not had any issues with the COUNTIFS only formula.

Thanks in advance for your help!!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!