Subtotal with more than one Function

[Abdul]

Hello!,

Is there a way in Excel (2007) that I can use more than one function
in a Subtotal?

For eg:

I want my Column A to use COUNTA and Column B:F to use SUM Function

Thanks

 

[Bob Phillips]

You could apply SUM to both columns and then go and change the subtotal
formula on one from =SUBTOTAL(9,... to =SUBTOTAL(3,... on each subtotal
(this could be done with Replace Ctrl-H).

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Abdul]

Thanks for your suggestion Mr. Bob,

I am doing the subtotal on a large dynamic range data using VBA.

It is a multi column data with several Locations and based on each
chnage of Location I am having a Subtotal (SUM) for many columns and I
need a COUNT of Data in one Column.

Is it possible to achieve this using VBA?

Thanks,

 

[Dave Peterson]

Just do the equivalent edit|replace in code.

with worksheets("somesheetnamehere")
.range("x1").entirecolumn.replace what:="=subtotal(9,", _
replacement:="=subtotal(3,", ....