How to count each instance of more than one contact in a contact list column?

JJL

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!

Paul Newcome

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)