Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
   If Intersect(Target, Range("D3:D5")) Is Nothing Then
        Range("D3:D5").ClearContents
        If Target.Cells.Count = 1 Then
             
        Else
            On Error Resume Next
             Range("D3").Value = WorksheetFunction.Average(Target)
             Range("D4").Value = WorksheetFunction.CountA(Target)
            Range("D5").Value = WorksheetFunction.Sum(Target)
            On Error GoTo 0
        End If
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
  
       If Not Intersect(Target, Range("A9:D9")) Is Nothing Then
        On Error Resume Next
        ActiveSheet.ShowAllData
        Range("A11").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A8").CurrentRegion
    If Target.Address = [C9].Address Then Range(Cells(12, 22), Cells(Rows.Count, 22)).SpecialCells(xlCellTypeVisible).Cells(1).Select
    End If  
End Sub