# Countif to create sequence number

Hi All,

I hopoing to use this formula as a column formula (to auto fill as rows are added), but if using absolute references it can not be used as a column formula.

Does anyone have any work arounds?

=COUNTIF($[TW Job number]$1:[TW Job number]@row, [TW Job number]@row)

0

## Answers

Hi, Daniel.

Here's one approach.

Create an auto-number column. Count the numbers in the range that are less than the current row's. Increment by one to get the next in the sequence.

Next_in_the_Sequence =

`COUNTIF([AutoNum]:[AutoNum], <[AutoNum]@row) + 1`

Using the auto-number column allows you to reorder the set without affecting the sequence--until you delete a row.

Thanks Toufong,

A great suggestion but I dont think its what I am looking for.

The formula I'm after is to count how many times the value on that row appears above the that row. So in row 5 of your table the count of Col_1 would be 3 A. Row 6 would be 2 C.

I see...then you'll want to count

Col_1. You still want auto-number as a reference because it determines the order of the rows you're interested in. Try...`= COUNTIFS([Col_1]:[Col_1],[Col_1]@row, [AutoNum]:[AutoNum], <[AutoNum]@row) + 1`

EXPLANATION: Count the cells in the

Col_1where the value is the same as that ofCol_1for the current row, AND the auto-number is less than the auto-number of the current row. And then add 1 (to increment).You can count

TW Job number, too, but you still need something else to reference or evaluate in order to determine precedence. (The best alternate to auto-number is Timestamp or "create date-time" but this assumes that 2 rows are never created at the same second, which is not true when you cut-paste multiple rows.)Hi @Toufong Vang ,

Sorry for hijacking this post but I'm really interested in your very last comment about "The best alternate to auto-number is Timestamp or "create date-time"" Can you please elaborate on how to get a timestamp or create date-time without using auto-numbers. I've been struggling trying to figure out a way to create an order in which rows are created that I could clear for a row when the row is changed in order for that row to have that number re-calculated and fall to the bottom of the sheet for processing. Any help would be appreciated.