-1

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

Shilpa
  • 98
  • 3
  • 19
  • You need to be much more specific. Describe the specific structure of your data using a simple example, show what calculation you're trying to do, and what the problem is with what you've tried – Amit Dec 09 '15 at 18:31
  • What stops you from excluding the header row from the formula... – EEM Dec 09 '15 at 18:32
  • Updated the question with more explanation – Shilpa Dec 09 '15 at 18:45
  • Are you using excel tables? – EEM Dec 09 '15 at 18:48
  • As the subtotal applied is `COUNTA` header is always counted, just subtract 1 (i.e. =-1+SUBTOTAL(3,XDO_?NUMBER?)` – EEM Dec 09 '15 at 18:51

1 Answers1

0

As the subtotal applied is COUNTA header is always counted, just subtract 1 use this formula"

=-1+SUBTOTAL(3,XDO_?NUMBER?)

EEM
  • 6,601
  • 2
  • 18
  • 33
  • If the first table doesn't change, then it would subtract 1 in the wrong way. So I was wondering if there is away to subtract based the column header – Shilpa Dec 09 '15 at 18:57
  • Please explain why. Isn't your header always included? Isn't your header always a label? Would you post a picture of your tables? and you still have not answer: Are these Excel tables? – EEM Dec 09 '15 at 19:00