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!
