I have a column like this
MIR548H2
MIR548H2
MIR548H2
AAGAB
AAGAB
AAGAB
AAGAB
AAGAB
AAGAB
AAGAB
Want the counts in increasing manner like this
MIR548H2 1
MIR548H2 2
MIR548H2 3
AAGAB 1
AAGAB 2
AAGAB 3
AAGAB 4
AAGAB 5
AAGAB 6
AAGAB 7Found solution
=COUNTIF($A$2:A2,A2)
This way each formula has to sift a range whose length increases by one per row. Assuming you have 10000 rows this means about 50 000 000 comparisons needed. A sheet has > 1 000 000 rows. Filled next to completely this makes about 500 000 000 000 comparisons.
In fact your data look as if you can assure that any specific content occurs in only one contiguous range. Exploiting this assurance you can reduce the expense in comparisons to N where N is the number of used rows.