Add Row To Table and Changes Row Above Column Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1683901%22%20slang%3D%22en-US%22%3EAdd%20Row%20To%20Table%20and%20Changes%20Row%20Above%20Column%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1683901%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20the%20screenshots%20you%20can%20see%20that%20I'm%20trying%20to%20add%20an%20instance%20counter%20that%20displays%201%20if%20it's%20the%20first%20subs%20in%20the%20table%2C%20a%202%20if%20its%20the%20second%20sub%20and%20so%20on.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20I'm%20having%20is%20whenever%20I%20add%20a%20new%20row%2C%20it%20autocorrects%20the%20formula%20above%20it%20to%20include%20the%20whole%20range.%20I%20want%20it%20to%20stay%20sequential%20so%20I%20get%20the%20proper%20count.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20all%20ears%20if%20there%20is%20a%20better%20way%20to%20do%20this%20or%20my%20formula%2Fsettings%20just%20need%20to%20be%20tweaked.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AndrewDavis_0-1600438105811.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219555i31AE531523E94AB0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22AndrewDavis_0-1600438105811.png%22%20alt%3D%22AndrewDavis_0-1600438105811.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AndrewDavis_1-1600438150989.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219556iB83D0218D057ED39%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22AndrewDavis_1-1600438150989.png%22%20alt%3D%22AndrewDavis_1-1600438150989.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1683901%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1685170%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20Row%20To%20Table%20and%20Changes%20Row%20Above%20Column%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1685170%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F798867%22%20target%3D%22_blank%22%3E%40AndrewDavis%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EHow%20about%20you%20add%20another%20%24%20in%20the%20formula%20before%20the%20last%20cell%20row%3F%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3Dif(C11%3D%22subs%22%2CCOUNTIF(%24C%247%3A%24C%2411%2C%22subs%22)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted

Hi @AndrewDavis,

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

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

 

Highlighted

@AndrewDavis 

As variant that could be

image.png

with

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

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

Highlighted