Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

  • Overachievers Alumni

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions