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
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
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 808
Downloads 240
CodeID
DB ID