Table formula range automatically changing...

[thatexcelguy]

I have a table in Excel with an array formula which works, however when I expand the table (add to it), the last row will then change the formula's end range by increasing it to the new range.

For example, if the table is currently on row 1055 and the range shows $B$2:B1055, when I press tab or drag the table down one more row, that range will change to $B$2:B1056 (and the newly created row $B$2:B1057), instead of staying the same as it was. It keeps wanting to use the last row in the table instead of the row it is currently on.

You can see a gif of what is happening here: https://gfycat.com/FrequentSlimyBlueshark

And I uploaded the Excel sample file here: https://ufile.io/20hyi

I tried changing the formulas to use INDIRECT and ROW, but it doesn't seem to work with my arrayformula.

Thank you in advance!

 

[thatexcelguy]

I replaced my arrayformula with the simple:

=IF(B2="Yes", 0, SUM(C1,1))

Thanks to user 'mrhstn' for suggesting that. I never did figure out why Excel was behaving as it was, but this is a far simpler formula and fixes the issue.

 

[pecoflyer]

We can even shorten this one to =(B2<>"yes")*(C1+1)