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.