Issue with Countif formula when used in table

[dquigley]

I'm building a spreadsheet and need to use countif to create a number for each instance of a value. I usually do this by using a countif formula to count all of the instances above it, like this:
=COUNTIF(A$2:A2,A2)
And as a drag this formula down, the range expands accordingly, so I would end up with:


ITEM #
APPLE 1
APPLE 2
APPLE 3
APPLE 4
ORANGE 1
APPLE 5
ORANGE 2
ORANGE 3

I want to use a formula like this in a table, so when the user makes an new entry in the item column, the formula for # is automatically filled in. However, every time I add a new row, the formula changes.

When I add a New value in A3, the formula I have in B2:
=COUNTIF(A$2:A2,A2)
automatically changes to
=COUNTIF(A$2:A4,A3)

And the new formula in B3 becomes
=COUNTIF(A$2:A4,A3)
as opposed to the one I expect:
=COUNTIF(A$2:A3,A3)

Anyone ever experience this or have a workaround or suggestion? Thanks so much

[Zer0Cool]

Without a sample file its hard to say whats causing the problem

[xlnitwit]

If you're going to use a table, it's better to convert to structured referencing if possible. Assuming your first column is called Item, you could use
=COUNTIF(INDEX([Item],1):[@Item],[@Item])