Help with Formula for Counting Unique Values

sarahh729

I created a tracking spreadsheet for our annual law school on campus events and on this spreadsheet, we list within the same column all of the attorney participants we send on campus for that law school. There's usually between 1-4 attendees per event and within each row. If there are multiple attendees for that event, they will be listed with the same row as a list of names and formatted as "last name, first name."

I've been trying to figure out how I can have Smartsheets tally how many times attendees are found within the column. I want to provide a nice report or table of which attorneys are being utilized the most for events.

I've tried many different formulas and so far, I've been able to determine how many attorneys we have listed for events (not helpful for my needs) and did successfully use a =COUNT(DISTINCT(COLLECT formula, listing one specific attendee within the formula. While this worked, I was hoping Smartsheets could automate this so I wouldn't have to run a formula on every unique attorney who attends events.

Is this possible?

Please let me know if I can provide more information to assist.

Mike TV

@sarahh729

Here's an example Events Sheet that I put together:

Here's a helper sheet which I set up to compile data from the Events Sheet above:

Here's the formula in the Events Attended column:

=COUNTIF({Events Sheet Participants}, CONTAINS(Participants@row, @cell))

Here's a report I put together from the above sheet which counts Events Attended for participants:

I'm using the above filter to filter out participants with zero Events Attended (J, K, and L Alice)

sarahh729

Hi, MikeTV. Thanks for your help here. I'm a bit confused as to how many sheets you used for this example and the formula you used. Would you be able to share the actual example sheets so I can review it within Smartsheets? I've tried to recreate your example to see how it works but I keep getting Invalid Arguments when I try your formula.

Also, for your second picture, did you have to type out all of the possible attendee names yourself or was another formula used to list them?