Countifsl using Contact List with multiple values

Hello,

I am trying to build a formula that solves the following.

  1. We are trying to determine who helps other team members assisting in completing a request
  2. Each Request may have more than one person supporting
  3. Column that serves as reference is Linked
  4. Range is a Contact list that allows multiple contacts

I can't return all of the users in a cell. I only return the first person in the cell. I am using the formulas below.

=COUNTIFS({CUST Request Log Range 1}, CONTAINS(Person@row, @cell)) - Returns 0's

=COUNTIFS({CUST Request Log Range 1}, FIND(Person@row, @cell) > 0) - Returns the first contact only

Trying to think about what else I may be missing for it to look for everyone in the cells

Thanks for any help you can provide.

Best Answer

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    Answer ✓

    @Keith Barres

    Try HAS like this:

    =COUNT(COLLECT({CUST Request Log Range 1}, {CUST Request Log Range 1}, HAS(@cell, Person@row)))

    I hope that helps.

    Cheers,

    Ramzi

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

Answers

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    Answer ✓

    @Keith Barres

    Try HAS like this:

    =COUNT(COLLECT({CUST Request Log Range 1}, {CUST Request Log Range 1}, HAS(@cell, Person@row)))

    I hope that helps.

    Cheers,

    Ramzi

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!