Count and provide unique numbers

Adrienne Van Halem

My need is to provide a unique and incremental number for each row, so when a new item is added it gives it the next number in that category. The current formula is

=COUNTIFS(Created:Created, <Created@row, Type:Type, Type@row) + COUNTIFS(Created:Created, Created@row, Type:Type, Type@row)

However, because it's based on the system created date, if multiple are pasted in at the same time, they have the same number, no longer making them unique.

How can I adjust this formula to ensure unique numbers?

The formula is in the Count column. And as you can see in the red box, this is an example of two rows with the same number when i need them unique.

I tried copying and pasting but the attachments get left behind. And if I move to another sheet and move back, it maintains the created date.

David Tutwiler

Any type of formula like that is going to change as you resort as you're counting the instances up to that point.

Unfortunately, Column Formulas can not contain cell or absolute references. So this formula would not be allowed.

David Tutwiler

I think this will do the trick:

=COUNTIF(Type$1:Type@row, Type@row)

This formula only counts from the first row to your current row, and counts how many of the same type of have in the preceding rows. So if this is the 23rd Action, it will be 23. The next row will be 24.

Adrienne Van Halem

https://community.smartsheet.com/discussion/comment/267219#Comment_267219

It seems to have helped but isn't allowing for a column formula because of the row syntax. And also, if its sorted differently, it's now a different number. Any thoughts around that?

Thanks for your feedback!

David Tutwiler

Any type of formula like that is going to change as you resort as you're counting the instances up to that point.

Unfortunately, Column Formulas can not contain cell or absolute references. So this formula would not be allowed.