COUNTIF cell HAS a contact from list of contacts
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
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Have you tried the second formula you have posted yet?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I have tried this but it returns 0 so doesn't seem to work.
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
That's worked, thank you! Simpler solution than I thought.
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!