Cross Sheet reference/"assigned to" allow multiple contacts per cell LIKE formula?

edited 12/09/19 in Formulas and Functions

SS community - I need help. I have created a Sales Pipeline sheet for my sales team, sales stages etc which is basically it's own CRM. I am trying to gain metrics in order to keep the sales team members honest and report the truth to the leadership team. 


Challenge: Cross sheet formula (below) pulls correct data but when the ASSIGNED TO column has "allow multiple contacts per cell" checked, the formula overlooks the searched for contact. Numbers are now skewed. 


Question: I want to the formula to pull all rows where assigned to (even when multiple contacts are present) pull Danielle so - CONTAINS or LIKE is needed. Does anyone know the right formula for this? 


=COUNTIFS({Sales Pipeline Range 3}, ="contacted", {Sales Pipeline Range Rep}, "Danielle")



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!