Star/Check only rows that are unique/not blank

Options

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

  • Darren Mullen
    Darren Mullen ✭✭✭✭✭✭
    Options

    @Mspencer

    My first question is can you correct the reason why there are duplicates in the first place?

    If not, it looks like you can check to see if there is no date in the "Difficult Conversations" column. Does this formula work for you?

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

  • Mspencer
    Mspencer ✭✭✭
    edited 08/09/23
    Options

    @Darren Mullen I wish, but the duplicates are because the spreadsheet is being fed from a registration form, so those duplicates are the same person registering multiple times to change their session dates or add more sessions. So I need to keep them.

    Your formula is close but it's not checking the boxes/starring those who attended. Is another way?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!