Using different summary functions in Data>Subtotal

[Fred Smith]

I have a 10-column list I want to subtotal. For some columns I want a Sum,
for others I want a Max. In Help, it says:

"Excel calculates subtotal values with a summary function, such as Sum or
Average. You can display subtotals in a list with more than one type of
calculation at a time."

But how?

I set the summary function to Max, and click the fields I want, then change
it to Sum and click the remaining fields, but it does a Sum on all fields.
Excel just remembers the last summary function when I click OK.

Is there a way to use more than one summary function in the same Subtotal?
I'm developing a macro for this, so if there's a way to do it in VBA, that
would be fine.

 

[Frank Kabel]

Hi Fred
after inserting the first subtotal (e.g. the sum) go again to 'Data -
Subtotals'. choose your function AND uncheck the first checkbox (should
read something like 'remove existing subtotals')

This should do

 

[Fred Smith]

The problem with this approach is it creates an additional subtotal line,
which I don't want.

I was able to solve my problem after I figured out the Data>Subtotal command
creates a summary line with a bunch of Subtotal functions in each column. I
just went along each summary line and changed the first parameter in the
Subtotal function. That worked for me.

 

[Dave Peterson]

Try selecting a column that you want to change (or multiple columns if you can
change them the same way).

Then edit|replace
=subtotal(9,
to
=subtotal(3,

This would change Sum's to CountA's.

Max is: =subtotal(4,

(check help for all 11 possibilities. Did you notice that they're in
alphabetical order?)