View Single Post
 
Old 05-12-2018, 09:46 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 949
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default

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:
Code:
=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.
Reply With Quote