COUNTIF cell HAS a contact from list of contacts

benjamin.hardie

I currently have a COUNTIF formula that counts cells if they contain any 1 of 7 contacts in a multi-contact column. I am using HAS 7 times within an OR function. Example below:

=COUNTIF({Range to count}, OR(HAS(@cell, "Contact 1"), HAS(@cell, "Contact 2"), HAS(@cell, "Contact 3"), HAS(@cell, "Contact 4"), HAS(@cell, "Contact 5"), HAS(@cell, "Contact 6"), HAS(@cell, "Contact 7"))

These 7 contacts are members in my team and I use this formula in several places throughout Smartsheet so whenever someone leaves or joins the team it means I have to adjust every formula individually.

I'd like to know if I can use the HAS function or similar to COUNTIF a cell has a value that appears in a list. I have made a Team List Smartsheet with all the names of my team members in one column so I would like to only have to adjust this list when team members come and go with all the formulas referring to this list. Something like:

=COUNTIF({Range to count}, HAS(@cell, {Any name from team list})

Please can someone help me with this?

Thank you!

Paul Newcome

Try swapping the range and criteria within the HAS function. I know I have done this before but can't remember right off exactly how.

Paul Newcome

Have you tried the second formula you have posted yet?

benjamin.hardie

https://community.smartsheet.com/discussion/comment/349402#Comment_349402

I have tried this but it returns 0 so doesn't seem to work.

Paul Newcome

Try swapping the range and criteria within the HAS function. I know I have done this before but can't remember right off exactly how.

benjamin.hardie

https://community.smartsheet.com/discussion/comment/349432#Comment_349432

That's worked, thank you! Simpler solution than I thought.

Paul Newcome

Happy to help. 👍️