## 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.