Excel Subtotals 1

[Sql7user]

Hi,

I have a problem with the subtotals function in Excel. Lets say I have 3 columns, and want to add subtotals for all 3. If I use ex SUM function for all 3 theres no problem but if I want to make column 1 and 2 with SUM and the third with Average?

Is this possible without having to do the subtotals in 2 steps? When I do it in 2 steps i get 2 rows instead of one with Total and Average. Im doing this in vba so if you want plz explain in vba code.

Thanks!!!

 

[SkipVought]

Hi,

If you are not using the SUBTOTAL Wizard, meaning that your table will not have intermidiate subtotals, just grand totals, you could use the SUBTOTAL function.

=SUBTOTAL(9,Range1) will SUM
=SUBTOTAL(1,Range2) will AVERAGE

See Help for the SUBTOTAL worksheet function for details.

This is actually what the SUBTOTAL wizard uses, because it will ignore subtotals in the range.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[BruceReed]

I found the easiest way was to use the wizard (I had 50k rows, and 4 levels of sub-sub totals). One the wizard was finished use search & replace to change the columns like Skip suggests. Trying to do this manually would have been a disaster.

IE leave column A alone, but in column B replace all "subtotal(9" with "subtotal(1". This way I got each column a different summary (average, total, max.), but use the power of the wizard.

Good luck
Bruce

 

[Sql7user]

Thanks for your excellent tips. It works perfect.

Sql7user