Formula to Count The First Or Second Time Something Has Appeared In A Sheet

Mil05006

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.

David Tutwiler

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.

Mil05006

https://community.smartsheet.com/discussion/comment/371387#Comment_371387

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))