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

jhawker

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

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

 

Try this...

jhawker

Paul - This WORKED!

Paul Newcome

Excellent! Happy to help! 

Maricarmen Vargas

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.

Thanks

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

jwilson970216

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