CODEWITHSUNDEEP
arraysvbasumifs
tmtran99
tmtran99

Reputation: 37

Sumifs for array and range vba excel

Let say I have the data in Excel like:

Amount  Quarter Delay
378.07  2018.25 6.00
378.07  2018.25 6.00
844.36  2018.25 3.00
1134.21 2018.25 3.00
1134.21 2018.25 5.00
1512.28 2018.25 4.00
99.42   2018.75 2.00
447.38  2017.25 2.00
646.22  2018.75 2.00
745.64  2018.75 2.00
745.64  2018.75 3.00
572.4   2016.25 8.00
572.4   2016.25 8.00
572.4   2016.25 8.00

The Amount, Quarter and Delay are in column C, D, E respectively.

And I have the following code:

Sub abc()

MsgBox Application.SumIfs(Range("C:C"), Range("D:D"), 2018.25, Range("E:E"), 3)
         
End Sub

The idea is that I want to take the sum where Quarter = 2018.25 and Delay = 3.

However, in the case that I save the data of Delay and Quarter in an array, let say "d_delay" and "d_quarter", the following code does not work:

Sub abc()

'assume that we have data in d_delay and d_quarter by redim and they have the length of 14

Set d_amount = Range("C2:C15")

MsgBox Application.SumIfs(d_amount, d_quarter, 2018.25, d_delay, 3)
         
End Sub

Can you give me advice on this?

Upvotes: 0

Views: 283

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

If you are going to use arrays, make the amount an array also and iterate them and do your own conditionals. Sum when the conditions are met:

Sub abc()
    Dim d_amount As Variant
    d_amount = ActiveSheet.Range("C2:C15")
    
    Dim d_quarter As Variant
    d_quarter = ActiveSheet.Range("D2:D15")
    
    Dim d_delay As Variant
    d_delay = ActiveSheet.Range("E2:E15")
    
    Dim ttl As Double
    ttl = 0#
    
    Dim i As Long
    For i = 1 To UBound(d_amount, 1)
        If d_quarter(i, 1) = 2018.25 And d_delay(i, 1) = 3 Then
            ttl = ttl + d_amount(i, 1)
        End If
    Next i
    
    MsgBox ttl
End Sub

enter image description here

Upvotes: 1

Related Questions