SumOf_3Filters

Returns sum of certain column in range (or table) in sheet after applying AutoFilter
It reset Autofilter, applies up to 3 filters, gets total (sum) then remove Autofilter from certain sheet, giving 1st top left cell (header cell).
This method is way faster than creating loop into those rows to check for the same condition.
Finally, a fine way to quickly filter data in sheet


Public

Tested

My Own Work
Function SumOf_3Filters(Sum1stCell, Filter1stCell, FilterSheet, Optional FilterWB = "This", _
                                Optional Filter1Column = 0, Optional Filter1List = "", _
                                Optional Filter2Column = 0, Optional Filter2List = "", _
                                Optional Filter3Column = 0, Optional Filter3List = "")
    ' Filters range in sheet, up to 3 filters, then reads the SUM of certain column
    ' Caller can provide ...
    '    Filter1stCell    = 1st cell in range, top left cell, 1st header cell, like C4
    '    FilterSheet    = Sheet name, like DB1
    '    FilterWB        = workbook name, like "This" to refer to ThisWorkbook or ToolData.xlsb
    '    Sum1stCell    = Top cell of Column we want to get total (Summation) from, like J4
    '    Filter1Column = Column where to apply Filter1, numeric number represent column number starting from Filter1stCell, like 3 if we have Filter1stCell as "C4" and we want to filter column E
    '    Filter1List    = List of one or more items to filter, can be 1 item or list of items separated by comma, like "Illinois,Texas,DC,Florida" or "Texas"
    '    Filter2Column = ...
    '    Filter2List    = ...
    '    Filter3Column = ...
    '    Filter3List    = ...
    '   
    ' Blank in any FilterXColumn will not apply filter
    ' Function will remove filter after finish
    '
    If FilterWB = "This" then FilterWB = ThisWorkbook.Name
   
    If Workbooks(FilterWB).Worksheets(FilterSheet).AutoFilterMode Then Workbooks(FilterWB).Worksheets(FilterSheet).Range(Filter1stCell).AutoFilter ' removes filter if found applied
    Workbooks(FilterWB).Worksheets(FilterSheet).Range(Filter1stCell).AutoFilter ' Apply filter again to start fresh
    If Filter1Column > 0 Then Workbooks(FilterWB).Worksheets(FilterSheet).Range(Filter1stCell).AutoFilter Filter1Column, Split(Filter1List, ","), xlFilterValues
    If Filter2Column > 0 Then Workbooks(FilterWB).Worksheets(FilterSheet).Range(Filter1stCell).AutoFilter Filter2Column, Split(Filter2List, ","), xlFilterValues
    If Filter3Column > 0 Then Workbooks(FilterWB).Worksheets(FilterSheet).Range(Filter1stCell).AutoFilter Filter3Column, Split(Filter3List, ","), xlFilterValues
    Rett = WorksheetFunction.Subtotal(9, Workbooks(FilterWB).Worksheets(FilterSheet).Range(Sum1stCell).EntireColumn)
    Workbooks(FilterWB).Worksheets(FilterSheet).Range(Filter1stCell).AutoFilter ' removes filter
    SumOf_3Filters = Rett
   
    ' If Not UCase(Left(LSelectsC1, 4)) = "[ALL" Then ShDB3.Range("C4").AutoFilter 3, Split(LSelectsC1, ","), xlFilterValues ' Category
    ' If Not UCase(Left(LSelectsC2, 4)) = "[ALL" Then ShDB3.Range("C4").AutoFilter 4, Split(LSelectsC2, ","), xlFilterValues ' Brand
    ' If Not UCase(Left(LSelectsC3, 4)) = "[ALL" Then ShDB3.Range("C4").AutoFilter 5, Split(LSelectsC3, ","), xlFilterValues ' Quality
    ' ShDB3.Range("C4").AutoFilter 7, Split(LSelect3, ","), xlFilterValues ' -------------------------                                Period1 - Open
    ' If LSta_OpenInc = "" Then
    '    Total1 = "N/A"
    ' Else
    '    ShDB3.Range("C4").AutoFilter 6, Split(LSta_OpenInc, ","), xlFilterValues ' -------------------------    Open Include states
    '    DoEvents
    '    Total1 = WorksheetFunction.Subtotal(9, ShDB3.Range("J4").EntireColumn)
    ' End If
End Function

Sum1stCell, Filter1stCell, FilterSheet, Optional FilterWB = "This", Optional Filter1Column = 0, Optional Filter1List = "", Optional Filter2Column = 0, Optional Filter2List = "", Optional Filter3Column = 0, Optional Filter3List = ""

Views 210 Downloads 43

VBA-Excel Database
ANmarAmdeen
787
Attachments
Revisions

v1.0