Need to get Sum of a Column based on values satisfying criteria in 6 different columns

[mrugeshpoojary]

I have a requirement to evaluate a sum of a single column, if 5 other columns satisfy individual criteria. Each of this columns would have array of values.
Considering following subset of data

DIFFERENCE_PERIOD_NET	PERIOD_NET_DR	PERIOD_NET_CR	PERIOD_NUM	SEGMENT1	SEGMENT2	SEGMENT3	SEGMENT4	SEGMENT5
570.83​	570.83​	0​	4​	01	720	7340	0000	000
570.83​	570.83​	0​	2​	01	720	7340	0000	000
570.83​	570.83​	0​	1​	01	720	7340	0000	000
570.83​	570.83​	0​	3​	01	720	7340	0000	000
570.83​	570.83​	0​	5​	01	720	7340	0000	000
94.5​	655.5​	561​	4​	01	000	1430	0000	000
0​	105.49​	105.49​	2​	01	000	1430	0000	000
50562.69​	144376752.5​	144326189.9​	1​	01	000	1430	0000	000

I have tried 2 approaches which gives desired result, however they don't meet the performance expectation as I would have to evaluate 500,000 rows on an average, and this logic will be part of UDF which means if this function is used several times in the different cell then this would be fired every time excel recalculates.
Below are my various approaches and I am pretty new to VBA coding learning everyday, I would appreciate your inputs on improving the below approaches or a completely new approach to achieve this.
Approach 1 : Using Autofilter along with Subtotal function to get the sum. This took 12 ms for 12K rows.

Worksheets("Data").Range("D2:D9").AutoFilter Field:=4, Criteria1:=period_num_arr, Operator:=xlFilterValues
Worksheets("Data").Range("E2:E9").AutoFilter Field:=5, Criteria1:=segment1_arr, Operator:=xlFilterValues
Worksheets("Data").Range("F2:F9").AutoFilter Field:=6, Criteria1:=segment2_arr, Operator:=xlFilterValues
Worksheets("Data").Range("G2:G9").AutoFilter Field:=7, Criteria1:=segment3_arr, Operator:=xlFilterValues
Worksheets("Data").Range("H2:H9").AutoFilter Field:=6, Criteria1:=segment4_arr, Operator:=xlFilterValues
Worksheets("Data").Range("I2:I9").AutoFilter Field:=7, Criteria1:=segment5_arr, Operator:=xlFilterValues
l_total = Application.WorksheetFunction.Subtotal(9, Worksheets("Data").Range("A2:A9"))
Worksheets("Data").AutoFilterMode = False 'For close to million records this takes 2 secs

Approach 2 : Adapted a function which I came across online and modified to handle 1D arrays as input. This takes around 3 secs for 12K rows

Private Function SumIfConditionsMetArray(ColToAdd As Long, arr As Variant, _
                       ParamArray Criteria() As Variant) As Double
    ' Returns:     The sum of values from a column where
    '              the row match the criteria.
    ' Parameters:
    ' 1) Arr:      An array in the form of arr(row,col) (
    '              (like the array passed by an excel range)
    ' 2) ColToAdd: Index of column you want to add. In this example this is 1
    ' 3) Criteria: a list of criteria you want to use for
    '              filtering, if you want to skip a column
    '              from the criteria use "Null" in the
    '              parameter list.

    Dim tot As Double
    Dim CountCol As Long, param_array_cnt As Long
    Dim r As Long, c As Long, d As Long
    Dim conditionsMet As Boolean, paramConditionsMet As Boolean
    Dim cExtra As Long
    Dim DimRow As Long, DimCol As Long
    DimRow = 1: DimCol = 2
    cExtra = 4    
    CountCol = UBound(Criteria)
    Dim A As Long
    Dim B As Long
    tot = 0
    For r = LBound(arr, DimRow) To UBound(arr, DimRow)
            A = r
        conditionsMet = False
        For c = LBound(Criteria) To CountCol
            B = c + cExtra            
            If Not IsZeroLengthArray(c) Then 'Custom function to evaluate if the passed Array is empty
                For d = LBound(Criteria(c)) To UBound(Criteria(c))
                    conditionsMet = False
                    paramConditionsMet = False
                    If CStr(arr(A, B)) = CStr(Criteria(c)(d)) Then
                        paramConditionsMet = True
                    End If                
                    If paramConditionsMet Then
                        Exit For
                    End If            
                Next d                
                If paramConditionsMet Then conditionsMet = True                
            End If                
            If Not conditionsMet Then
                Exit For
            End If
        Next c
        B = ColToAdd
        If conditionsMet Then
            tot = tot + arr(A, B) 'Adding the value
        End If
    Next r
    SumIfConditionsMetArray = tot 'Returning the calculated sum
End Function

Possible Approach 3 : I have also played around with SUM(SUMIFS()), but this doesn't return desired value. Example below for the above sample data
=SUM(SUMIFS(A2:A9,D2:D9,{1,2,4},G2:G9,{"1430","7340"}))
This gives 51133.52 whereas the correct value is 52369.68