I am trying to use subtotal to calculate the totals for a set of columns. The position of the rows can change for different scenarios. Since the position of the rows are fixed, I am unable to set a range. At present, the subtoal always includes the column header when calculating the total. Is there a way where in I can ignore the column header while calculating the subtotal for a particular column.
Update: I have 2 tables in the excel sheet. Depending on the number of rows from the first table, the rows from the second table are shited down. I am trying to calculate the subtotal for the second table. Since the position of the second table is not fixed, I can not use the cell position to calculate the total.
Right now i am calculating the total as "=SUBTOTAL(3,XDO_?NUMBER?)" in the template, where xdo_?number is the defined name used for the column. Since the position is not fixed, its inlcuding the header value too there by increasing the total calculation by 1. I do not know how to exclude the header value based on the column name. I cannot give the header position since its not fixed and it keeps changing. The header name of that column is Number so I was wondering if there is way I could calculate totals by ignoring based on the column header name.
TIA
As the subtotal applied is COUNTA header is always counted, just subtract 1 use this formula"
=-1+SUBTOTAL(3,XDO_?NUMBER?)