Subtotal with Variable Row

It is common to have subtotal on top of each table. 

Most people use subtotal to get total amount of visible row when filtered. That is SUBTOTAL(9,.......). 

What if... There are consistent changes in data in the table resulted in changes to number of row (reduce or increase).  

Most people will change the row number in subtotal formula manually. However, this is error-pro as human being, we always forgot! 

Here is the magic formula to address the issue. The formula will cover until the last row when there are changes in subtotal range. Valid until column Z only.

SUBTOTAL(9,INDIRECT(CHAR(COLUMN()+64)&ROW()+2):INDIRECT(CHAR(COLUMN()+64)&COUNTA($A:$A)+2)) 

Where:

INDIRECT function returns a reference to a range. 

CHAR(65) is A. 

CHAR(66) is B. and so on.

COLUMN() will return current column number. 

Example: 

COLUMN(A1) = 1 

COLUMN(B2) = 2 

CHAR(COLUMN(A1)+64)=CHAR(1+64)=CHAR(65)=A 

ROW() will return current row number. 

ROW()+2 is my first row to sum. Why +2? Because my current row contains the subtotal formula. +1 is header/ title. My range starts from +2. +2 is variable depending on the gaps from cell row 1 to the header. 

COUNTA is to count non-blank.

COUNTA($A:$A) is counting non-blank range in column A. I prefer column A because it is always the beginning of my data.