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)

Find more posts tagged with

Accepted answers

All comments

TVang

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.

Daniel Douglas

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.

TVang

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 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 to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.