Subtotal Formula

[Joeman187]

Hi Chandoo,


Hope all is well in the new year. I have a couple of questions regarding the subtotal formula for you. I want to add some rows to a section of my subtotal and I know to use the offset function, but where do you put it within the subtotal formula? Also I would like to learn how to link the subtotal formula to a table. Your input would be greatly appreciated so I can move on to another area of Excel. Joe, from Michigan.

 

[Jai9]

Hi,


Try this.


=SUBTOTAL(9,OFFSET(A1,1,0):OFFSET(A1,10,0))


This would add A2:A11.


Not sure about second question though.


Jai

 

[Joeman187]

Boy you guys rule, thats what i was looking for. you know what youre talking about, and you respond in a promt manner, thanks again can you pass the second question to any of your collegues? Thanks again, I'm glad I found your website. Joe, from Michigan.

 

[Jai9]

Hi Joe,


You are welcome!!!


I am sure someone will come along and take care of the second question.


Jai

 

[SirJB7]

Hi, Joeman187!


Assuming your table Tabla1 is like this:

-----

[pre]

A	B
1	100
1	200
3	300

[/pre]
-----


The following formula will display a 6:

=SUBTOTAL(2,Tabla1)


Is that what you were asking?


Regards!

 

[Joeman187]

Hi SirJB7,


Can you explain what Tabla means? Thank you.

 

[SirJB7]

Hi, Joeman187!


When you create a table in a workbook Excel assigns by default a name Table1 for English versions and Tabla1 for Spanish versions. That's why I wrote "... your table Tabla1 is like...", referring to the name of the table.


Give a look at this file with the related sample:

https://dl.dropbox.com/u/60558749/Subtotal%20Formula%20%28for%20Joeman187%20at%20chandoo.org%29.xlsx


Regards!