finding the sum of filtered columns

[Jaffy]

Is there a way to add a certain column that is filtered by
a date range using a macro to filter and unfilter the
sheet. Examle: column A's total unfiltered is 100. Total
filtered is 7. But by using sum(A4:A34) in the total
column still results in a calculation of the total column
filtered or unfiltered. Thanks in advance for any help

 

[MS]

=SUBTOTAL(9,A4:A34)

 

[MS]

=SUBTOTAL(9,A4:A34)

put in the range that you want to total... I think I have the wrong range
for your needs

maybe change it to...

=SUBTOTAL(9,A4:A104)
then once filtered the subtotal will only sum the rows actually showing on
the sheet.

 

[Gord Dibben]

Jaffy

Use the SUBTOTAL Function.

=SUBTOTAL(9,A4:A34)

See Help on SUBTOTAL for more options. The 9 is for Sum. There are 11 in
all.

Gord Dibben Excel MVP - XL97 SR2 & XL2002

 

[Debra Dalgleish]

Perhaps calculation has been set to Manual.
Choose Tools>Options, and on the Calculation tab, select Automatic.

 

[Jaffy]

Check that, was set on auto. Thanks for the help

 

[Debra Dalgleish]

What is the code that you're using to filter the table?
Could it be hiding the rows, instead of filtering them?

 

[Jaffy]

I am not versed in the macro lang. Here is the code for
the Apply filter. As I said before I used this one from a
sample sheet I believe was created by you. At your name
was on the macros. Thanks for your patience.

Option Explicit

Sub ApplyFilter()
Range("A3:A34").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("O23"), Unique:=False
End Sub

Sub RemoveFilter()
On Error Resume Next
ActiveSheet.ShowAllData
End Sub

 

[Debra Dalgleish]

Add a line at the end of the ApplyFilter, to force the recalculation,
and see if that helps. Replace "R2" with the cell that contains your
subtotal:

Sub ApplyFilter()
Range("A3:A34").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("O23"), Unique:=False
Range("R2").Calculate
End Sub

 

[jaffy]

Did noy work, thanks

-----Original Message-----
Add a line at the end of the ApplyFilter, to force the recalculation,
and see if that helps. Replace "R2" with the cell that contains your
subtotal:

Sub ApplyFilter()
Range("A3:A34").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("O23"), Unique:=False
Range("R2").Calculate
End Sub




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.