Sumifs from an Array in Excel VBA

Question

I want to do a sumif from an array but i am not sure how to reference a full column in an array. For instance i have the following data in excel (in columns A and B) and code that works fine,

RR TT 1 J 2 K 3 J 4 K 5 J 5 K 6 J 7 K 8 J 9 K

 Sub test() 
 Dim s As Range 
 Dim s2 As Range
 Set s = Range("A2:A11") 
 Set s2 = Range("B2:B11")
 e = WorksheetFunction.SumIfs(s, s2, "J")
 MsgBox e 
 End Sub

This sums the RR column where the TT column equals "J" and the answer is 23. But in the code i assign each column to a seperate Range in VBA. I would like to assign both columns to an array and do the sumifs from the array. The first part of the code would then look as follows,

Dim s() As Variant
ReDim s(1 To 10, 1 To 2)
s = Range("A2:B11")

How do i then reference the columns of the array in the sumifs function? (the first two entries in the sumifs function)

e = WorksheetFunction.SumIfs(?, ?, "J")

I will at the end work with a much bigger dataset and if it is possible i would like not create a ton of seperate Ranges but just one array.

As @Rory wrote, you cannot. You'll need to write your own, looping through the array. — Ron Rosenfeld
– Ron Rosenfeld, Commented Feb 23, 2015 at 13:47

Phylogenesis · Accepted Answer · 2015-02-23 13:48:16Z

1

You could create a custom function to do this:

Public Function SumIf(lookupTable() As Variant, lookupValue As String) As Long
    Dim I As Long

    SumIf = 0

    For I = LBound(lookupTable) To UBound(lookupTable)
        If lookupTable(I, 1) = lookupValue Then
            SumIf = SumIf + lookupTable(I, 2)
        End If
    Next I
End Function

answered Feb 23, 2015 at 13:48

Phylogenesis

7,90024 silver badges27 bronze badges

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

Comments

snb · Accepted Answer · 2015-02-23 23:33:15Z

1

Sub M_snb()
  msgbox [sum((A1:A9)*(B1:B9="J"))]
End Sub

or

Sub M_snb()
  msgbox [sumproduct((A1:A9)*(B1:B9="J"))]
end sub

or

Sub M_snb()
  msgbox [sum(if(B1:B9="J",A1:A9,0))]
end sub

edited Feb 23, 2015 at 23:33

answered Feb 23, 2015 at 14:46

snb

3431 silver badge4 bronze badges

Comments

Jason Samuels · Accepted Answer · 2015-02-24 07:25:14Z

0

Thanks for the comments and the answers. I don't have to use arrays but it was my first choice and keeping with ranges is fine. I did find got it right with Ranges. And i did the following

 Sub test() 
 Dim s As Range 
 Set s = Range("A2:B11") 
 e = WorksheetFunction.SumIfs(s.Columns(1), s.Columns(2), "J")
 MsgBox e 
 End Sub

This also gives me what i want.

answered Feb 24, 2015 at 7:25

Jason Samuels

9716 gold badges24 silver badges43 bronze badges

Collectives™ on Stack Overflow

Sumifs from an Array in Excel VBA

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related