#1
|
|||
|
|||
SUBTOTAL FUNCTION PROBLEM when add a new line on top
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? |
#2
|
|||
|
|||
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]) 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. |
#3
|
|||
|
|||
Quote:
Bytheway I found a solution to my problem. I just leave a free row! So If I want Code:
=SUBTOTAL(9;D3:D26) I left D3 empty and I start from D4 in order to include D4 (the new row)in my function! Thank you a lot. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
2016 Excel Subtotal Function Changed | CindyPauls | Excel | 0 | 08-31-2017 11:44 AM |
Problem with Value function | misokol | Excel | 3 | 01-11-2017 08:59 AM |
Problem with function | PRA007 | Word VBA | 2 | 01-13-2016 01:11 AM |
Function problem | li roy | Excel | 2 | 02-18-2012 02:44 AM |
Each subtotal signature require only at last page for each subtotal. | aligahk06 | Excel | 0 | 05-07-2010 11:07 PM |