Add Row To Table and Changes Row Above Column Formula

New Contributor

Hello,

 

From the screenshots you can see that I'm trying to add an instance counter that displays 1 if it's the first subs in the table, a 2 if its the second sub and so on. 

 

The problem I'm having is whenever I add a new row, it autocorrects the formula above it to include the whole range. I want it to stay sequential so I get the proper count.

 

I'm all ears if there is a better way to do this or my formula/settings just need to be tweaked.

 

Thanks

 

AndrewDavis_0-1600438105811.png

AndrewDavis_1-1600438150989.png

 

4 Replies

Hi @AndrewDavis,

How about you add another $ in the formula before the last cell row? 

=if(C11="subs",COUNTIF($C$7:$C$11,"subs"),"")

 

@AndrewDavis 

As variant that could be

image.png

with

=IF([@Sub]="subs",COUNTIF($C$7:INDEX([Sub],ROW()-ROW(Table1[[#Headers],[ID]]),1),"subs"),"")

@Sergei Baklan I tweaked it to fit in my table, but that worked perfectly. Thanks