How to count each instance of more than one contact in a contact list column?
Hi Everyone,
I have a sheet that contains two columns that are contact list columns with another column that states the project (known as a 'Class') that those contacts worked on
In another sheet, I have the following formula:
=COUNTIF(COLLECT({Hazard Assessments Range 1}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$1, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 2}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$1, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 1}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$2, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 2}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$2, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 1}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$3, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 2}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$3, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 1}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$4, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 2}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$4, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 1}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$5, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 2}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$5, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 1}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$6, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 2}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$6, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 1}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$7, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 2}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$7, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 1}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$8, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 2}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$8, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 1}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$9, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 2}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$9, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 1}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$10, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 2}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$10, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 1}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$13, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 2}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$13, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 1}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$14, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 2}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$14, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 1}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$15, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 2}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$15, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 1}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$16, @cell) > 0) + COUNTIF(COLLECT({Hazard Assessments Range 2}, {Hazard Assessments Range 6}, @cell = Class@row), FIND($[Team Members]$16, @cell) > 0)
The above formula currently works for going through the sheet and counting each instance of those specific contacts associated with each 'Class'  but, it is pretty long and almost hitting Smartsheet's 4000 character limit in a cell. I need to somehow condense the above formula so that I can add in more contacts for it to search for. So far, I've come up with the following:
=COUNTIF(COLLECT({Hazard Assessments Range 1}, {Hazard Assessments Range 6}, @cell = Class@row), OR(FIND($[Team Members]$1, @cell) > 0, FIND($[Team Members]$2, @cell) > 0, FIND($[Team Members]$3, @cell) > 0, FIND($[Team Members]$4, @cell) > 0, FIND($[Team Members]$5, @cell) > 0, FIND($[Team Members]$6, @cell) > 0, FIND($[Team Members]$7, @cell) > 0, FIND($[Team Members]$8, @cell) > 0, FIND($[Team Members]$9, @cell) > 0, FIND($[Team Members]$10, @cell) > 0, FIND($[Team Members]$13, @cell) > 0, FIND($[Team Members]$14, @cell) > 0, FIND($[Team Members]$15, @cell) > 0, FIND($[Team Members]$16, @cell) > 0)) + COUNTIF(COLLECT({Hazard Assessments Range 2}, {Hazard Assessments Range 6}, @cell = Class@row), OR(FIND($[Team Members]$1, @cell) > 0, FIND($[Team Members]$2, @cell) > 0, FIND($[Team Members]$3, @cell) > 0, FIND($[Team Members]$4, @cell) > 0, FIND($[Team Members]$5, @cell) > 0, FIND($[Team Members]$6, @cell) > 0, FIND($[Team Members]$7, @cell) > 0, FIND($[Team Members]$8, @cell) > 0, FIND($[Team Members]$9, @cell) > 0, FIND($[Team Members]$10, @cell) > 0, FIND($[Team Members]$13, @cell) > 0, FIND($[Team Members]$14, @cell) > 0, FIND($[Team Members]$15, @cell) > 0, FIND($[Team Members]$16, @cell) > 0))
But, it is not counting all of the instances and as a result comes up short on its count compared to the previous formula. I believe this is because of the 'OR' function and so, when there are two or more of the contacts I'm looking for in the same cell, the formula currently only counts it as one  when I need it to count each instance of a specific contact in each cell.
Does anyone have any suggestions for how I can condense the first formula? Any help is greatly appreciated! Thank you in advance!
Answers

A COUNTIFS and HAS functions may be more effective. You can also rename your cross sheet references to something a bit shorter. I have even had to go so short in some formulas as to use single letters.
=COUNTIFS({Assigned}, OR(HAS(@cell, [Team Members]1), HAS(@cell, [Team Members]2)), {Class}, @cell = Class@row)
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!