COUNTIF cell HAS a contact from list of contacts

benjamin.hardie
edited 11/01/22 in Formulas and Functions

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!

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!