Need help with a formula

mrseibt

I am trying to get a formula for my Speaker Position column that will rank each speaker if there are multiple on a given date. I have tried using the AI generate formula and it keeps coming back with an error. Please help!

James Keuning

So you have three people all speaking at 10:10. So each of these people is number 1? And what happens on the second day? They are all number 2? Or do they reset to number 1 again?

You should fil out that Speaker Position with your desired result, and explain the logic of how that result is determined, and then we can help create the formula.

mrseibt

There is only 1 speaker a day. Those signed up on the same day are the backups in case the first person can not make it. So in the example above, in the Speaker Position column, I would need it to return the value of 1 on row 113, 2 on row 114, and 3 on row 115. I tried the AI formula generator inside Smartsheet and it kept giving me an error.

jpaul

@mrseibt , there doesn't appear to be any information within the row for the formula to know who is 1,2 or 3. Adding a column and naming them "Primary", "Backup 1", and "Backup 2" would allow for a formula to set their precedence.

mrseibt

@jpaul the names get onto the sheet by people filling out a form. If I created a new column and used the Created Date, would it be able to make a formula based on this? They might all submit a response on the same day, but times will most likely be different. Could that work?

Jgorsich

Add a helper column title "row" that just has the row numbers.

Here is a thread where various methods were described:

https://community.smartsheet.com/discussion/69476/row-numbers

once you've got that, make your formula something that counts all instances of your speaker and time on that row and earlier rows, like this "=countifs([speaker and time]:[speaker and time],[speaker and time]@row,row:row,< = row@row)"