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")



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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


    Try this...

  • Paul - This WORKED!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Excellent! Happy to help! yes

  • Hello!

    @Paul Newcome I'm trying this as well but it is not working, using @cell, so I tried to insert reference to the sheet, but still won't work.

    I want to count

    Tasks that are In Development, they are also Overdue and are also assigned to X Person

    =COUNTIFS({Master Pipeline - Priorities}, "Overdue", {Master Pipeline - Assigned To}, CONTAINS("Andre Carlo", {Master Pipeline - Assigned To}), {Master Pipeline Statuses}, ="In Development")

    The result is 0, but I can see that the number SHOULD be 4.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this...

    =COUNTIFS({Master Pipeline - Priorities}, "Overdue", {Master Pipeline - Assigned To}, CONTAINS("Andre Carlo", @cell), {Master Pipeline Statuses}, ="In Development")

    If the Assigned To column is a Contact type column though, the CONTAINS function will not work (it doesn't like contact types), so we would need to switch over to a FIND function.

    =COUNTIFS({Master Pipeline - Priorities}, "Overdue", {Master Pipeline - Assigned To}, FIND("Andre Carlo", @cell) > 0, {Master Pipeline Statuses}, ="In Development")

  • ^^ The FIND solution worked for my "multiple contacts per cell" column. Super-helpful cleanup. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!