SUMIF From VBA

There are three ways we can be involved with SUMIF() when we are using VBA.

• The Range.Formula(), Range.FormulaR1C1() and Range.FormulaArray() Properties

This is a straightforward process. To enter a formula containing SUMIF() we use the Range.Formula or Range.FormulaR1C1 properties:

Sub Example1()

    '=SUM(SUMIF(B2:B7,{"Smith";"Williams"},D2:D7))
    Sheet1.Range("C1").Formula = "=SUM(SUMIF(B2:B7,{""Smith"";""Williams""},D2:D7))"

End Sub

If the formula needs to be a CSE formula, we use the Range.FormulaArray property:

Sub Example2()

    '{=SUM(SUMIF(B2:B7,F2:F3,D2:D7))}
    Sheet1.Range("C1").FormulaArray = "=SUM(SUMIF(B2:B7,F2:F3,D2:D7))"

End Sub

Just as when you manually type in an array formula, you should not include the { } at the beginning and end. Excel will automatically add these for you.

• The WorksheetFunction Class

If we want to conditionally sum using the SUMIF() worksheet function in VBA, we can access it via the WorksheetFunction class.

Sub Example3()

    Dim varResult As Variant

    '=SUMIF(B2:B7,"Smith",D2:D7)
    varResult = Application.WorksheetFunction.SumIf( _
                    Arg1:=Sheet1.Range("B2:B7"), _
                    Arg2:="Smith", _
                    Arg3:=Sheet1.Range("D2:D7"))

End Sub

With this method, if SUMIF() returns an error then we have to add an error handler in VBA to handle it. I have included the named arguments in the example so you can see how uninformative they are and the little benefit intellisense gives us. Ideally we would like to see range, criteria and sum_range, but instead we have to work with arg1,arg2 and arg3.

Another way is to call it from the Application class:

Sub Example4()

    Dim varResult As Variant

    '=SUMIF(B2:B7,"Smith",D2:D7)
    varResult = Application.SumIf( _
                    Sheet1.Range("B2:B7"), _
                    "Smith", _
                    Sheet1.Range("D2:D7"))

End Sub

Using this approach, an error returned by SUMIF() will be held within the vResult variable which we can then handle with the CVErr() function. For further information and comparisons between these approaches have a read of this thread at dailydoseofexcel.

We come back to the earlier point (section 3.1) that the 1st and 3rd parameters expect range references. When you are calling SUMIF() via the WorksheetFunction class, do not be tempted to include any additional brackets around the range argument you are passing in.

Sub Example5()

    Dim varResult As Variant

    'type mismatch or object required error
    varResult = Application.WorksheetFunction.SumIf( _
                    (Sheet1.Range("B2:B7")), _
                    "Smith", _
                    Sheet1.Range("D2:D7"))

End Sub

The extra brackets around Sheet1.Range("B2:B7") cause that range to be de-referenced into an array which, as was mentioned the previous blog post, is unacceptable.

•   Application.Evaluate() and Worksheet.Evaluate() Methods

For the more complex SUMIF() formulas, the easiest way to calculate them directly within VBA is to use the Worksheet.Evaluate method. This method (more often than not) gives the same result as when the formula has been CSE entered, so we can treat normal and CSE formulas in the same way.

Sub Example6()

    Dim varResult As Variant

    '=SUM(SUMIF(B2:B7,{"Smith";"Williams"},D2:D7))
    varResult = Sheet1.Evaluate("=SUM(SUMIF(B2:B7,{""Smith"";""Williams""},D2:D7))")

    '{=SUM(SUMIF(B2:B7,F2:F3,D2:D7))}
    varResult = Sheet1.Evaluate("=SUM(SUMIF(B2:B7,F2:F3,D2:D7))")

End Sub

Again, errors returned by the evaluated expression will be stored within the variant variable. The Evaluate method is qualified by the Sheet1 codename, so all of the unqualified range references within the string expression will be considered to belong to that sheet.

Advertisements