Countif to create sequence number

06/30/21
Answered - Pending Review

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

  • Loucas TsiartasLoucas Tsiartas ✭✭✭✭✭

    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.

Sign In or Register to comment.