Column formula possible to count instances sequentially in an ever growing list

CBrown

Probably a dream but, is it possible to have a column formula that would count, in sequence, the instances in a list.

The first time a Name appears on a list would = 1

The second time a Name appears on a list would = 2

I have it solved with a formula but it can not be a column formula because of the variable piece.

=IF(DOS@row = "", "", COUNTIF($SURGEON$1:$SURGEON2855, SURGEON@row))

Samuel Mueller

With a couple helper columns, you can create a column formula to achieve this.

You need an autonumber column, then you need a helper column lets say called Row, the formula in row would be =match(helpercolumn@row, helpercolumn:helpercolumn, 0) - this gives you the row number that's referenceable, which you can add into your countif criteria

=IF(DOS@row = "", "", COUNTIF($SURGEON:$SURGEON, SURGEON@row, row:row, <row@row))

CBrown

Hello thanks for the response.

Got the Helper Column going (from the unique ident column) but can't seem to get the New Count working

=COUNTIF($SURGEON:$SURGEON, SURGEON@row,COUNT HELP:COUNT HELP,<COUNT HELP@COUNT HELP))

Samuel Mueller

@CBrown

You need brackets around Count help and no Dollar signs. and make sureit is countifs

=COUNTIFS(SURGEON:SURGEON, SURGEON@row, [COUNT HELP]:[COUNT HELP],<[COUNT HELP]@row))

CBrown

Ok, tried that and it's still not working... darn it. I can't figure what syntax is wrong.

Does it make a difference if the auto# column (my unique ident) is NOT is perfect sequential order? over time rows have been dragged up or down depending on the DOS (Date)

=COUNTIFS(SURGEON:SURGEON, SURGEON@row,[COUNT HELP]:[COUNT HELP],<[COUNT HELP]@row))

Samuel Mueller

@CBrown this is my fault, don't need that last parentheses

=COUNTIFS(SURGEON:SURGEON, SURGEON@row,[COUNT HELP]:[COUNT HELP],<[COUNT HELP]@row)

The match formula will always be in order, so if you put that formula in count help column, and reference count help, you should be good.

CBrown

ahhhh, go it! boom!

i added +1 at the end, it was showing the first instances as 0

TY very much Samuel!!

Samuel Mueller

Glad it's working!