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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!