Thanks:  0
Likes:  0

1. I have a workbook to keep track of Financial data for my department. Autofilter is turned on, when I change the filter I need the subtotal to change only I am using sumproduct instead of subtotal. Here is the formula =IF(ISBLANK(\$A\$3),SUMPRODUCT((\$F\$10:\$F\$472=\$F477)*(\$H\$10:\$H\$472)),SUMPRODUCT((\$F\$10:\$F\$472=\$F477)*(\$B\$10:\$B\$472=\$A\$3)*(\$ H\$10:\$H\$472)))

Cell A3 is to be updated with the new criteria filter, the problem is that cell A3 will not update until I click on the worksheet. Below is my code and everything seems to work except for the updating of cell A3. Can you tell me what I am missing.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Changefilters

End Sub

Public Sub Changefilters()
Dim w As Worksheet
Dim filterarray()

Set w = Worksheets("INS fy03")

With w.AutoFilter
With .Filters
For f = 1 To .Count
With .Item(f)
If .On Then
w.Range("A3").Value = .Criteria1
End If
End With
Next
End With

End With

End Sub

2. Try using Private Sub Worksheet_Calculate()

3. And, I'd suggest changing

=IF(ISBLANK(\$A\$3),SUMPRODUCT((\$F\$10:\$F\$472=\$F477)*(\$H\$10:\$H\$472)),SUMPRODUCT((\$F\$10:\$F\$472=\$F477)*(\$B\$10:\$B\$472=\$A\$3)*(\$ H\$10:\$H\$472)))

to

=IF(ISBLANK(\$A\$3),SUMIF(\$F\$10:\$F\$472,\$F477,\$H\$10:\$H\$472),SUMPRODUCT((\$F\$10:\$F\$472=\$F477)*(\$B\$10:\$B\$472=\$A\$3)*(\$H\$10:\$H\$4 72)))

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•