SUMIF function VBA code in Excel


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.

1 thought on “SUMIF function VBA code in Excel”

Comments are closed.