How to make SUBTOTAL formula range to automatically extend to include newly added rows

[Sean15]

Hi:

This formula:

=SUBTOTAL(9,I2:I4489)

does not auto extend to include newly added rows, example of I added row 4490

Could you help please?


Regards,

Sean

 

[Joe4]

If you add your new row between the range I2:I4489 (instead of adding the new row AFTER that range), it will automatically grow to increase it.
I will sometimes place a blank row at the end of my data and insert above this blank row to ensure that happens, while always adding new rows at the bottom.

If that is not possible, please answer the following:
- In your example above, what cell are you placing that formula in?
- Is there any other data currently under I4489?

 

[Marcelo Branco]

Maybe...

=SUBTOTAL(9,I2:INDEX(I:I,MATCH(9.99E+307,I:I)))

M.

 

[Marcelo Branco]

If that is not possible, please answer the following:
- In your example above, what cell are you placing that formula in?
- Is there any other data currently under I4489?

Relevant questions

M.

 

[Sean15]

Hi:

formula is in I4490

Data ends at I4489


I put this formula together using old Excel notes:

=SUBTOTAL(9,I1:OFFSET(I4490,-1,0))

It seems to work but I can't explain how it does.

Thanks for your help.

Regards,

Sean

 

[Joe4]

The key is this part right here:
OFFSET(I4490,-1,0))

Since you are placing the formula in cell I4490, as you insert rows and move the formula down, that range reference will move with it.
And the Offset function is telling it to use the row just above the range address in that formula (making it dynamic).