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

Options
✭✭✭✭✭

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

• ✭✭✭✭✭✭
Options

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭
Options

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)

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

• ✭✭✭✭✭✭
Options

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭
Options

I like the notification idea! How original!

• ✭✭✭✭✭✭
Options

Happy to help. Thanks for using the Community.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭
Options

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)