Formula to calculate... This is the Xth time this appears in this column.

Automations 1

Hi,

I'm looking for a formula that will tell me what number time something appeared in a specific column... I don't want a count of how many times it appeared in column, rather I want to know this is the 1st time, or 2nd time etc. that it is in the column.

Ex:

Apple 1

Orange 1

Apple 2

Bannana 1

Orange 2

Apple 3

Grape 1

Mark Cronk

Hi @Rivky Emert

I'll use [fruit] as the name of the column you want counted. lnsert a text/number helper column [Fruit Count] with this formula:

=[Fruit]@row +": "+ COUNTIF([Fruit]$1:[Fruit]@row, [Fruit]@row)

The formula will count up from current row to row 1 and return the number of times the fruit@row appeared and combine it into a text string to get the result you're looking for.

Work?

Mark

Leibel S

@Rivky Emert

If you wanted this as a column formula:

Add the below 2 helper columns:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

Your count formula would be:

=COUNTIFS([Fruit]:[Fruit], [Fruit]@row, [ROW#]:[ROW#], <=[ROW#]@row)

Mark Cronk

Hi @Rivky Emert

I'll use [fruit] as the name of the column you want counted. lnsert a text/number helper column [Fruit Count] with this formula:

=[Fruit]@row +": "+ COUNTIF([Fruit]$1:[Fruit]@row, [Fruit]@row)

The formula will count up from current row to row 1 and return the number of times the fruit@row appeared and combine it into a text string to get the result you're looking for.

Work?

Mark

Automations 1

Yes, thank you!

The only hesitation is that it can't be saved as a column formula because of the absolute reference. I always get nervous about formulas that aren't a column formula...

Mark Cronk

Me too. Column formulas are far better when they can be used. If you add an IFERROR, copy the formula to the bottom of your sheet and lock the column it should be pretty stable. You could set up a notification that if [fruit] isn't blank and [fruit count] is blank to notify you so you can ensure the formula copies down correctly.

=IFERROR([Fruit]@row +": "+ COUNTIF([Fruit]$1:[Fruit]@row, [Fruit]@row),"")

Good luck.

Mark

Automations 1

I like the notification idea! How original!

Mark Cronk

Happy to help. Thanks for using the Community.

Mark

Leibel S

@Rivky Emert

If you wanted this as a column formula:

Add the below 2 helper columns:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

Your count formula would be:

=COUNTIFS([Fruit]:[Fruit], [Fruit]@row, [ROW#]:[ROW#], <=[ROW#]@row)

Automations 1

Thanks @Leibel S it works!