## To totally unlock this section you need to Log-in

Login

The Microsoft Excel

**SUMIF**function adds all numbers in a range of cells, based on a given criteria. If we want to use or automize some operations with VBA in Excel there are three ways we can be involved with

**SUMIF()**when we are using

**VBA**:

**The Range.Formula(), Range.FormulaR1C1(), 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 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.

## FAQs

**Question**:* I have a question about how to write the following formula in Excel.*

*I have a few cells, but I only need the sum of all the negative cells. So if I have 8 values, A1 to A8 and only A1, A4 and A6 are negative then I want B1 to be sum(A1,A4,A6).*

**Answer**: You can use the **SUMIF **function to sum only the negative values as you described above. For example:

=SUMIF(A1:A8,"<0")

This formula would sum only the values in cells A1:A8 where the value is negative (ie: <0).

**Question**: *In Microsoft Excel I'm trying to achieve the following with IF function:*

*If a value in any cell in column F is "food" then add the value of its corresponding cell in column G (eg a corresponding cell for F3 is G3). The IF function is performed in another cell altogether. I can do it for a single pair of cells but I don't know how to do it for an entire column. Could you help?*

*At the moment, I've got this:*

=IF(F3="food"; G3; 0)

**Answer**: This formula can be created using the SUMIF formula instead of using the IF function:

=SUMIF(F1:F10,"=food",G1:G10)

This will evaluate the first 10 rows of data in your spreadsheet. You may need to adjust the ranges accordingly.

I notice that you separate your parameters with semi-colons, so you might need to replace the commas in the formula above with semi-colons.

SUMIF function VBA code in Excel – http://heelpbook.altervista.org/2014/sumif-vba/ #howto #heelpbook @heelpbook #excel #function #formula #microsoft