Assign sequence numbers to rows based on Year in a table and stick with the records

Occasional Visitor



I am looking to assign sequence numbers to rows based on Year, and but when a new year starts, restart numbers as well. Please see the example below, I was able to use =COUNTIF($B$2:B2,B2) to populate the number to each row, but the problem I have is how can I make the seq # to stick with each row when I sort the records. currently with countif, if I sort the record by timestamp either newest to oldest or vice versa, it will always stay as this sequence.

1/31/2023 20:4120231
1/31/2023 20:4120232
1/31/2023 20:4620233
1/31/2023 20:4920234
2/1/2023 11:2020235
2/1/2023 11:3920236
2/1/2023 15:2820221
2/1/2023 15:5220222





2 Replies


If you want the numbers to stick, you cannot use formulas.

So after using the formulas to generate the sequence numbers, select the column, copy it (Ctrl+C), then paste special (Ctrl+Alt+V) as values. This replaces the formulas with their result.


Typically you would need to incorporate the full date/time into the formula

= 1 + COUNTIFS(year, year, date,"<"&date)

and not rely on position.