Formula to Count The First Or Second Time Something Has Appeared In A Sheet
Hello,
We're currently using a sign up sheet for people to attend some additional trainings but we're limited to 2 people per training and we don't want everyone to have access to the training information to change it, so we created a list of all trainings for people to look at. Each training has a unique ID attached to it and to sign up you have to fill out a simple form. Currently each person has to put down if they are the first or second person signing up for the training manually.
Is there a formula(s) I can run that will let me know if this is the first time or second time the unique ID has appeared on the sign up sheet? (or if it is the third + time and we need to alert them that the training is full). Happy to use helper columns etc. to make this work but can't figure out a way to get smartsheet to go "this is the first time this ID has appeared, this is the second time" in a way that I can use to say "you are attendee 1, you are attendee 2."
I'm pulling their names from the sign up back to the list of trainings and putting their name into the attendee 1 and attendee 2 fields there.
Here's a list of my fields currently.
Answers
-
It won't be a column formula because of the static row that you have to define, but you can drag this formula down to your last filled in row and it will copy it for all new rows added in.
=COUNTIF([Event ID]$1:[Event ID]@row, [Event ID]@row)
This formula will give you a running count of how many times the unique event ID has come up on the sheet.
-
Thank you for your response! I used this and kept working on this with a collogue of mine and we came up with this after poking at it for a LONG while and it completely works!
=COUNT(COLLECT([Row ID]:[Row ID], [Event ID]:[Event ID], [Event ID]@row, [Row ID]:[Row ID], [Row ID]@row <= [Row ID]@row))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!