Star/Check only rows that are unique/not blank

I'm trying to count the number of unique attendees for a training session on a registration form by pulling data from both the registration form AND the roster for the specific session's date.

My current formula is

=if(countifs({08/08 DC Registrant},[Email Address]@row,{08/08 DC Attended},@cell=1)>0,1)

and it works, HOWEVER, if an attendee has registered multiple times, it is starring/checking the box for every one of their rows instead of one, which results in an inaccurate/higher attendee count. Total count/starred boxes should be 11; it is currently 14.

How can I star/check only ONE row for each duplicate attendee? See screen shot. AJ and Paige are my duplicates.


I have tried inserting the following into the working formula above but none have worked:

(distinct([Email Address]:[Email Address])

([Difficult Conversations]:[Difficult Conversations],<>""

(not(isblank([difficult conversations]:[difficult conversations]))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!