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
-
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)
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!