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 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_1 where the value is the same as that of Col_1 for 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.
Help Article Resources
Categories
Check out the Formula Handbook template!