Excel table object: How to increment numbers when value changes in another column?

Question

I have a range of cells in Table format in excel. How to increment numbers in a table column when value changes in each group with a formula?

I tried using the formula B2=COUNTIF($A$2:A2,A2) which creates a number sequence that restarts for every change in another column. This works well for tables with fixed number of rows. The problem arrives when adding new rows to the table since the range shifts to adjust to the table range. Example for row 2: FROM COUNTIF($B$2:B2;B2) to COUNTIF($B$2:B3;B2) instead of keeping COUNTIF($B$2:B2;B2) unchanged.

How to avoid such behaviour when adding new rows?

Desired result:

Answer 1

David, if your cells are not formatted as an Excel Table, this formula can do the job:

=COUNTIF(INDIRECT("A1:A" & COUNTA(A:A)),A1)-COUNTIF(INDIRECT("A"&ROW() & ":A" & COUNTA(A:A)),A1)+1

Answer 2

I don't get the same results as you do -- when I insert a row, the criteria cell changes.

In any event, since this is a Table, you can use structured references:

B2: =COUNTIF(INDEX([Product],1):@[Product],@[Product])

Answer 3

Since it's a table, I'd suggest structured references like:

=COUNTIF(Table1[[#Headers],[Product]]:[@Product],[@Product])