Using contact on a multi-contact column as condition for countifs.
I have the following formula. The range is a multi-contact column. There may be a single contact or several. The Contact8 references another cell with a single contact. I want to count any of the cells in the range that contain the contact. It may be mixed in with several others. I was trying to use the HAS or CONTAINS function but could not get the formula to resolve.
=COUNTIFS({Portfolio Summary Rollup Range BB}, Contact8, {Portfolio Summary Rollup Range 3}, <14, {Portfolio Summary Rollup Range Status}, "Execute") / COUNTIFS({Portfolio Summary Rollup Range BB}, Contact8, {Portfolio Summary Rollup Range Status}, "Execute")
Answers
-
CONTAINS will not work with Contact Type columns. You will want to use the HAS function like so...
=COUNTIFS({Portfolio Summary Rollup Range BB}, Contact8, {Portfolio Summary Rollup Range 3}, <14, {Portfolio Summary Rollup Range Status}, "Execute") / COUNTIFS({Portfolio Summary Rollup Range BB}, HAS(@cell, Contact8), {Portfolio Summary Rollup Range Status}, "Execute")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!