Sumifs for array and range vba excel

Question

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?

Sumifs does not accept arrays. If you are going to load into arrays, just loop them and do the conditional with Ifs and sum when satisfied, it will be nearly as quick. — Scott Craner
– Scott Craner, Commented Aug 28, 2020 at 20:07

Scott Craner · Accepted Answer · 2020-08-28 20:17:36Z

1

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

answered Aug 28, 2020 at 20:17

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tmtran99 Over a year ago

In case that I have many lines, using the loop will take more time than using the sumifs?

Collectives™ on Stack Overflow

Sumifs for array and range vba excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related