finding the sum of filtered columns

  • Thread starter Thread starter Jaffy
  • Start date Start date
J

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
 
=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.
 
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
 
Perhaps calculation has been set to Manual.
Choose Tools>Options, and on the Calculation tab, select Automatic.
 
What is the code that you're using to filter the table?
Could it be hiding the rows, instead of filtering them?
 
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("O2:P3"), Unique:=False
End Sub

Sub RemoveFilter()
On Error Resume Next
ActiveSheet.ShowAllData
End Sub
 
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("O2:P3"), Unique:=False
Range("R2").Calculate
End Sub
 
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("O2:P3"), Unique:=False
Range("R2").Calculate
End Sub




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

.
Click to expand...
 
Back
Top