VBA SUMIF

What is VBA SUMIF function?

The VBA SumIf function is used to calculate the sum of the values in a range that meets specified criteria. It works similarly to the worksheet function SUMIF in Excel. For better clarity on how to use the VBA SumIf function, see the example below.

A subroutine is created to implement the VBA SumIf function for the range “K1-K13.” Here, the condition is that only the numbers greater than four are added, and the rest are left behind.

The sum of the numbers greater than 4 in the table mentioned above is as follows. The sum of numbers greater than 4 in the range is 38.

Syntax

The Syntax of calling the VBA SumIf function is:

Application.WorksheetFunction.SumIf(Range, Criteria, [SumRange])

where,

• Range: This is the range of cells that you want to evaluate against the given criteria.
• Criteria: This is the condition that you want to apply to the cells in the range.
• SumRange (optional): This is the range of cells that you want to sum if their corresponding cells in the range meet the specified criteria. If you omit this argument, SumIf will sum the cells in the range.

How to use VBA SUMIF function?

Below are the steps to follow to use the VBA SumIf function in a few simple steps.

Step 1: Select the “Developer” option in the new Excel workbook. It will be there in the toolbar.

Under the section, click on “Visual Basic” in the left-hand corner. This opens the Excel VBA Editor.

In the editor, in the title bar, click the “Insert” button and select the “Module” option.

Step 2: Declare the subroutine, which will be based on the number selected (200 or 800).

Step 3: Select the cell where you want to print the sum (for 200).

Step 4: Use the VBA SumIf function to add the values provided the adjacent column was equal to 200.

In range A1-A10, cells equal to 200 are only selected and added with the adjacent values in the range “C1-C10.”

Step 5: Define another cell to print the sum of values where column A had the value 800.

Step 6: Like Step 4, call the VBA SumIf function where the cells with the value 800 are considered in column A and added with their adjacent values in Column B.

Step 7: Print the values in a message box.

Use the VBA constant available in the MsgBox function to print the values in different lines. It is called the vbCrLf constant. 

vbInformation is used to embed an icon into the message box to make it more visually appealing.

Code:

Sub criteriaRange()
Dim sumRng As Range
Set sumRng = ThisWorkbook.Sheets(“Sheet3”).Range(“F7”)
sumRng = WorksheetFunction.SumIf(Range(“A1:A10”), 200, Range(“C1:C10”))
Dim sumRng2 As Range
Set sumRng2 = ThisWorkbook.Sheets(“Sheet3”).Range(“F12”)
sumRng2 = WorksheetFunction.SumIf(Range(“A1:A10”), 800, Range(“C1:C10”))
MsgBox “The price with $200 tax for each object: ” & sumRng.Value & vbCrLf & _
“The price with $800 tax for each object: ” & sumRng2.Value, vbInformation
End Sub

Step 8: To run the subroutine, click “F5” or the “Run” icon on the activity bar in the Excel VBA Editor.

The sums are printed in cells F7 and F12.

Examples

Given below are the different ways in which you can use the VBA SumIf formula with variable range values in real-world applications.

Example #1

Suppose you have a table with electricity and utilities and a list of people who are paying for it. You want to find out how many people with similar surnames are paying the bill. Here, you want to find the amount those with the name “Mendez” are paying for the electricity bill.

Step 1: Define the subroutine to find the total amount the Mendez family pays monthly.

Step 2: Define the range to calculate a range variable.

Step 3: Define the range of the table where the criteria for adding the values are defined.

Step 4: Define the cell in which the sum of the column will be printed. Then, call the VBA SumIf function with variable range as arguments. The “*” in the condition signifies that there is an important letter before or after it depending on where you put it. For more specifications, for example, the word “Mendez” need not be written completely and can be auto-filled with trial and error using the “?” symbol.

“*ende?” can be roughly translated to “Mendez”.

Step 5: Just as you found the sum of the Electricity bills paid, you can similarly do by finding the total Utility amount paid by the Mendez family.

Define the “Utilities” column to a range variable.

Step 6: Similarly, call the VBA SumIf function with variable ranges and provide the same criteria as explained in Step 4.

Step 8: Print the answers in a Message Box with a new line space between the values using the constant vbCrLf.

Code:

Sub SumIfCustomerNameContains()
Dim sumRange1 As Range
Set sumRange1 = Range(“B2:B11”)
Dim criteriaRange As Range
Set criteriaRange = Range(“D2:D11”)
Range(“C13”) = WorksheetFunction.SumIf(criteriaRange, “ende?”, sumRange1) Dim sumRange2 As Range Set sumRange2 = Range(“C2:C11”) Range(“C14”) = WorksheetFunction.SumIf(criteriaRange, “ende?”, sumRange2)
MsgBox “Amount spent by the Mendez family for Electricity: $” & Range(“C13”).Value & vbCrLf & _
“Amount spent by the Mendez family for Electricity: $” & Range(“C14”).Value
End Sub

Step 9: Press “F5” to run the code. Select the subroutine you want to run.

After running the subroutine, a message box pops up.

Then go to the worksheet and see the answers.

Example #2

Suppose you have a table of expenses, but in the category, you realize that there are multiple categories under the same name. You want to find the amount you spent in the last month on buying food from the restaurants.

Here, “Hotel” and “Hotels” mean the same. The sum can be found using the VBA SumIf table column where you can use the VBA Wildcards in the function to perform the calculations entirely automatically.

Step 1: Define the subroutine to find the total amount you paid solely in restaurants.

Step 2: Define a ListObject variable and define the table of contents in the Object.

Step 3: Get all the values under the column of the table you want to find the sum of. Using ListColumns, name the column and take all the data under the column.

Step 4: Define another range for the second column.

Step 5: Call the VBA SumIf function with the asterisk “*” after the word “Hotel”. “*” includes an extra word after the word is defined. This ensures that both “Hotel” and “Hotels” are counted for the next profit bracket.

Step 6: Print the amount spent using the Message Box fu

Code:

Sub SumOfTableForSumIf()
Dim tbl As ListObject
Set tbl = ThisWorkbook.Sheets(“Sheet1”).ListObjects(“Table1”)
Dim columnRange As Range
Set columnRange = tbl.ListColumns(“Price”).DataBodyRange
Dim columnRange2 As Range
Set columnRange2 = tbl.ListColumns(“Name”).DataBodyRange
Dim SumRan As Integer
SumRan = WorksheetFunction.SumIf(columnRange2, “Hotel*”, columnRange)
MsgBox “The amount spent on restaurants: $” & SumRan, vbInformation
End Sub

Step 7: Press “F5” to run the code. The output is displayed as shown below:

With this, you have learned how to use Wildcards in the VBA SumIf table column.

Example #3

In this example, you will learn how to assign variable ranges for the VBA SumIf function.

It can be used to find the amount paid by the individual “Diaz”.

Step 1: Define the subroutine to find the total amount that Mr.Diaz has to pay.

Step 2: Define the criteria range which will select the cells to be summed indirectly.

Step 3: Set the range to be added to another range variable.

Step 4: Define an integer variable to store the sum values. Here the “*” is used to check if there is any important data missing.

Step 5: Print the amount of money paid by Diaz using the MsgBox function.

Code:

Sub FindDiaz()
Dim r1 As Range: Set r1 = Worksheets(“Sheet2”).Range(“K2:K11”)
Dim r2 As Range: Set r2 = Worksheets(“Sheet2”).Range(“L2:L11”)
Dim sum As Integer: sum = WorksheetFunction.SumIf(r1, “Diaz*”, r2)
MsgBox “The amount spent by Diaz is: $” & sum, , “Diaz’s finances”
End Sub

Step 6: Run the subroutine to see how much Mr. Diaz has paid.

Important Things To Note

• Ensure your criteria are clear and descriptive to accurately capture the cells you want to include in the sum.
• Test your SumIf function with various criteria to ensure it accurately captures the desired cells for summation.
• Optimize your VBA code for performance, especially when dealing with large datasets, by minimizing unnecessary calculations and iterations. Avoid using the VBA SumIf loop statements since they raise syntax errors.
• Avoid hardcoding ranges or criteria directly into your VBA code. Instead, use variables or named ranges in VBA to make your code more flexible and easier to maintain.
• Avoid using overly complex criteria that may make your SumIf function difficult to understand or debug.
• If you’re using temporary helper columns or ranges for your SumIf function, don’t forget to clear them after the calculation to avoid cluttering your worksheet.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand VBA SUMIF, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA SUMIF. Here we explain how to use SUMIF function in Excel VBA and syntax along with examples & downloadable excel template. You can learn more from the following articles –

• VBA SUMIFS
• VBA SUM
• VBA CHECKBOX