SUBTOTAL FUNCTION PROBLEM when add a new line on top

[excelino]

Hello,
I use at cell C1 this function

=SUBTOTAL(9;(C2:C12))

If I add a line at the top and the data from C2 qoes to C3 ecc the new data at C2 it's not be calculate by the function.

I'm not sure if I explain well, because my English it's really bad, but I want to keep the function ordel also when I add a new line so from

=SUBTOTAL(9;(C2:C12))


-----------------------------> NEW LINE C2
=SUBTOTAL(9;(C2:C13))
ecc

is this possible?

[ArviLaanemets]

Leave at least 2 empty rows of sheet. Into row below those empty rows, enter column headers for your table, e.c. Header1, Header2, Header3, ... Format all columns according your needs.

Select all headers you just entered, and at least one empty row below header row. From menu, select Insert, and then from Insert menu pane Table. A popup window is opened. Check "My table has header row", and click <OK>. A Design menu will be activated. At left of Design menu pane, locate Table Name field - there is default name for created Table displayed (something like Table1). Rename the table so the name is meaningful for you, like e.g. MyTable, and press Enter. The newly creeated table is renamed;

Let's assume you want to sum all autofiltered entries in column with header "Header3" into cell C1. Enter into cell C1 the formula:

=SUBTOTAL(9;MyTable[Column3])

Enter data into Table. When prepared rows in defined Table are filled, to create a new row:
1. Simply enter a new value into any cell into next row below. A new Table row is created;
2. Select lowest right cell in table, and press Tab. A new empty Table row is created below current last row;
3. Select any cell in table and activate Design Menu. In Design Menu pane, at left is menu item "Resize Table" - click on it. A popup window is opened where the current Table range is displayed - edit the table range and click <OK>. New empty rows/columns are added to your Table MyTable, or rows/columns are removed from it - depending how you edited the Table size.

Whatever number of rows you enter into MyTable, the SUBTOTAL() calculates the sum of all autofiltered entries in field Column3 of Table MyTable.

NB! Whatewer you enter into cells above Table header, the row immediately above the Table header MUST REMAIN EMPTY! Otherwise Excel may include those entries into Table, and locate the Table header into some upper row - messing up with your Table and with your formulas.

[excelino]

Originally Posted by ArviLaanemets

Leave at least 2 empty rows of sheet. Into row below those empty rows, enter column headers for your table, e.c. Header1, Header2, Header3, ... Format all columns according your needs.

Select all headers you just entered, and at least one empty row below header row. From menu, select Insert, and then from Insert menu pane Table. A popup window is opened. Check "My table has header row", and click <OK>. A Design menu will be activated. At left of Design menu pane, locate Table Name field - there is default name for created Table displayed (something like Table1). Rename the table so the name is meaningful for you, like e.g. MyTable, and press Enter. The newly creeated table is renamed;

Let's assume you want to sum all autofiltered entries in column with header "Header3" into cell C1. Enter into cell C1 the formula:

=SUBTOTAL(9;MyTable[Column3])

Enter data into Table. When prepared rows in defined Table are filled, to create a new row:
1. Simply enter a new value into any cell into next row below. A new Table row is created;
2. Select lowest right cell in table, and press Tab. A new empty Table row is created below current last row;
3. Select any cell in table and activate Design Menu. In Design Menu pane, at left is menu item "Resize Table" - click on it. A popup window is opened where the current Table range is displayed - edit the table range and click <OK>. New empty rows/columns are added to your Table MyTable, or rows/columns are removed from it - depending how you edited the Table size.

Whatever number of rows you enter into MyTable, the SUBTOTAL() calculates the sum of all autofiltered entries in field Column3 of Table MyTable.

NB! Whatewer you enter into cells above Table header, the row immediately above the Table header MUST REMAIN EMPTY! Otherwise Excel may include those entries into Table, and locate the Table header into some upper row - messing up with your Table and with your formulas.

I really appreciate your response! I'm sure that you are talking about something very interesting but unforunately my knowledge on excel are not enought!
Bytheway I found a solution to my problem.
I just leave a free row!
So If I want

=SUBTOTAL(9;D3:D26)

on D2
I left D3 empty and I start from D4 in order to include D4 (the new row)in my function!
Thank you a lot.