multiple subtotals

[nfrichar]

Hi

I need to write a macro to add different types of sub totals to an excel sheet. I want to count one column and then sum others. I want then what the sheet to only display the sub totals, but leave the user the ability to drill down onto the underlying data

Any ideas

 

[SkipVought]

Hi,

Have you looked at Data/Subtotals...

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[nfrichar]

Hi Skip

Looked at Data/SubTotals but this will only allow me to sum all coulms or count all columns. I want to mis this up. Count say column a, sum columns b,c,d

 

[SkipVought]

What you want can be done with TWO data/Subtotal operation. Just UNCHECK Replace Current Subtotals and change the aggregation criteria.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[nfrichar]

Skip
Tried this but this gives me a new line for each subtotal which I do not want.

 

[SkipVought]

So you use one of them, like the sum and stuff the count in with a procedure using

cnt = application.counta(MyCountRange,CriteriaValue)

OR...

just insert a new column that has a value of 1 for each row and inclued that SUM that to get the count.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}