subtotal calculation

[Erin]

(ignore previous post, I hit send before I was done...)

I have a spreadsheet similar to this:

Year City State Amount
03 Albertville AL 300
04 Albertville AL 300
Variance 0

03 Alexander City AL 24
04 Alexander City AL 60
Variance 36

03 Andalusia AL 180
04 Andalusia AL 240
Variance 60

I am programatically inserting the Variance row and
calculating the difference between the two years. This
works well.

Now, I want to provide a grand total of all the
variances. I can do this in code as well, but I really
want to use the subtotal function so that if a user filters
on a particular state/city, etc. the subtotal will only
represent the visible rows.
I tried building a subtotal calculation programatically in
code, but ran into a limit of how many cells could be
included in the formula.

Any other ideas on how to do this so that the users can
filter the data and have the totals re-calculated?

 

[Tom Ogilvy]

Why not use the built in Subtotal function?

Also, you said you are adding variances? I am not sure that is meaningful.

 

[Guest]

I couldn't get the built-in subtotal function to calculate
DIFFERENCES between two numbers (only sums). I need it to
show the difference in customers ordered amount across the
2 years.

Anyway... I've figured it out. I'm still calculating
the variance rows myself, but then I'm also copying those
off to some hidden columns to the right on the
spreadsheet. My sub-totals at the bottom are then based
on those hidden columns. So, when a user filters, it then
re-calculates just perfectly!

Thanks for the help anyway.