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)
Answers

Hi, Daniel.
Here's one approach.
Create an autonumber 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 autonumber column allows you to reorder the set without affecting the sequenceuntil 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 autonumber 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_1 where the value is the same as that of Col_1 for the current row, AND the autonumber is less than the autonumber 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 autonumber is Timestamp or "create datetime" but this assumes that 2 rows are never created at the same second, which is not true when you cutpaste 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 autonumber is Timestamp or "create datetime"" Can you please elaborate on how to get a timestamp or create datetime without using autonumbers. 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 recalculated and fall to the bottom of the sheet for processing. Any help would be appreciated.
Help Article Resources
Categories
Check out the Formula Handbook template!