Excel SUMIFS VBA: How To Write + Examples - Blog

Summing up cells based on specific conditions using SUMIFS is one of the most common tasks Excel users do. You may need to perform those sums using VBA when doing some parts of an automation task. In this post, we will show you how to write Excel SUMIFS in VBA with examples.

Automate data import to Excel with Coupler.io

Get started for free

VBA SUMIFS function

SUMIFS is an Excel worksheet function. In VBA, you can access SUMIFS by its function name, prefixed by WorksheetFunction, as follows:

WorksheetFunction.SumIfs(...)

Or,

Application.WorksheetFunction.SumIfs(...)

When working with VBA code in Excel, you will be using the objects provided by the Excel object model. The top of the hierarchy is the Application object, and it has many properties, including the WorksheetFunction. If a worksheet function is used without an object being specified, then Application is automatically supplied as the object. So, in this case, you can safely omit the Application object qualifier to make your code shorter.

Excel VBA SUMIFS syntax and parameters

When you access SUMIFS in VBA, you’ll see the following syntax:

As you can see in the above image, SUMIFS returns Double. The last argument is [Arg29], but actually, the function has up to 255 arguments, which allows for 127 range-criteria pairs. This makes SUMIFS great for summing up values based on multiple criteria.

See the following explanation of the SUMIFS arguments.

Arguments or parameters

Name	Required/Optional	Description
Arg1	Required	sum_range. The range of cells to sum.
Arg2, Arg3	Required	criteria_range1, criteria1. The pair of the first criteria range and criteria.
[Arg4, Arg5], …	Optional	[criteria_range2, criteria2], …Additional pairs of criteria range and criteria.

Notice that the first pair of the criteria range and the criteria is required, while the rest are not. Thus, you can also use it for single criteria like SUMIF Excel.

Advanced filters using operators & wildcards in SUMIFS VBA

When using SUMIFS to filter cells based on certain criteria, you can use operators and wildcards for partial matching. This is part of the reason why this function is so powerful when used correctly!

Operators

Here are the operators you can use with your criteria:

• “>” (greater than)
• “>=” (greater than or equal to)
• “<” (less than)
• “<=” (less than or equal to)
• “<>” (not equal to)
• “=” (equal to — but it’s optional to use this operator when using “is equal to” criteria)

Wildcards

Use the following wildcard characters in the criteria to help you find similar but not exact matches.

• A question mark (?) matches any single character. For example, Ja?e matches “Jade” and “Jane”
• An asterisk (*) matches any sequence of characters.For example, Ja* matches “Jane”, “Jade”, “Jake”, and “James”
• A tilde (~) followed by ?, *, or ~ matches a question mark, asterisk, or tilde.For example, Discount~* matches “Discount*”

How to write SUMIFS in VBA

Knowing how to perform Excel functions in VBA can be a great benefit if you’re dealing with Excel every day. You may need to write SUMIFS in VBA as part of a bigger task. For example, every week, you need to automatically import data from multiple sources, process the data, summarize it using SUMIFS, and finally send a report in MS Word or Powerpoint.

Using VBA, you can import data from multiple external sources, clean it, and prepare it for further analysis and reporting. However, if you want an alternative to importing data without coding, use Coupler.io. This reporting and data automation solution allows you to import data from over 70 cloud sources to Excel and aggregate, combine, sort, filter, and organize your datasets. Try it yourself for free – just select the needed data source in the form below and click Proceed.

Now, let’s see a basic example of writing Excel SUMIFS in VBA. You can download the following file to follow along:

SumIfs – Basic example.xlsm

VBA SUMIFS: Basic example

Suppose you have a workbook containing order data in Sheet1, as shown in the following screenshot. Using VBA, you want to sum the Order Total for orders with a discount based on the value in cell I4, which is $15.

If you look closely at the above screenshot, the result is expected to be $955, which results from $185+$285+$485. The following steps show you how to create a sub-procedure (macro) in VBA to get the result using SUMIFS:

1. Press Alt+11 to open the Visual Basic Editor (VBE). Alternatively, you can open the VBE by clicking the Visual Basic button of the Developer tab.

2. On the menu, click Insert > Module.

3. In the code window, type the following procedure:

Sub SumOrderTotalByDiscount()     Range("J4") = WorksheetFunction.SumIfs(Range("G2:G11"), Range("F2:F11"), Range("I4")) End Sub

The code adds up the cells in range G2:G11 if the discount value in F2:F11 equals the value in I4. Then, it outputs the result in J4.

4. Run the code by pressing F5.
5. Check your worksheet. In cell J4, you should see $955.

VBA SUMIFS: How to use variables

You can use variables to label the data with more descriptive names. This way, your code can be more legible and easier to understand by other people. Take a look at the following procedure that uses variables and outputs the result of SUMIFS in a message box.

Sub SumOrderTotalByDiscountUsingVariables() ' Declaring variables Dim sumRange As Range Dim criteriaRange As Range Dim criteria As Integer Dim result As Double ' Assigning values to variables Set sumRange = Range("G2:G11") Set criteriaRange = Range("F2:F11") criteria = Range("I4") ' Calculating the result using the SUMIFS function result = WorksheetFunction.SumIfs(sumRange, criteriaRange, criteria) ' Displaying the result in a message box MsgBox "The sum of Total Order with a discount of $" & criteria & " is $" & result & "." End Sub

Result:

In the above SumOrderTotalByDiscountUsingVariables() code, we declared variables within the scope of the procedure. This means they can only be used in the procedure and will no longer exist once it ends. So, in this case, there’s no need to add Set variable = Nothing for each variable just before they go out of scope.

Executing SUMIFS in VBA macro using a button

Let’s say you need to build code in VBA for someone who may not be familiar with the VBE. They might not know how to run the code using the editor or even what the Macro dialog box looks like. In this case, using buttons can be very useful because even a novice will know that clicking on one typically makes something happen.

In this example, we will take the running SumOrderTotalByDiscountUsingVariables() procedure one step further by executing it on a button click.

Here are the steps:

1. Click the Developer tab, then click Insert > Button (Form Control).

2. Click and drag anywhere on the worksheet to create a button.
3. In the “Assign Macro” dialog, select SumOrderTotalByDiscountUsingVariables, then click OK.

4. If you want, click the button’s text and edit it to “Calculate” to give it a more descriptive name.

5. Now, test the button by clicking on it. You’ll see a message box appear showing the result.

More VBA SUMIFS examples: Single & multiple criteria

Now, let’s take a look at some SUMIFS examples below in VBA. We cover examples with single and multiple criteria, as well as examples with different data types for the criteria.

VBA SUMIFS less than numeric criteria

The following SUMIFS VBA code sums the Order Total for orders with numbers less than 1004006.

Sub SumIfOrderNumberLessThan()     Dim sumRange As Range     Dim criteriaRange As Range     Dim criteria As Long     Set sumRange = Range("G2:G11")     Set criteriaRange = Range("A2:A11")     criteria = Range("I4").Value         Range("J4") = WorksheetFunction.SumIfs(sumRange, criteriaRange, "<" & criteria) End Sub

We assign the criteria variable with the value from cell I4. Then, to make the “less than” filter work, we combine the “<” operator with criteria using an ampersand symbol. Also, notice that we use a Long data type for the criteria variable because Integer won’t be enough to store a 7 digit order number value.

VBA SUMIFS date criteria

In this example, we’re summing the total discounts for orders made on 11/18/2021.

Sub SumDiscountsByOrderDate()     Dim sumRange As Range     Dim criteriaRange As Range     Dim criteria As String     Set sumRange = Range("F2:F11")     Set criteriaRange = Range("B2:B11")     criteria = Range("I4").Value         Range("J4") = WorksheetFunction.SumIfs(sumRange, criteriaRange, criteria) End Sub

We use a String data type for the criteria variable. After assigning it with the value from I4, the value will be “11/18/2021“. The “is equal to” criteria is used in the SUMIFS function, but as you can see, it’s not necessary to use the “=” operator.

VBA SUMIFS string criteria with wildcards

The following SUMIFS VBA sums the total discounts for orders made by customers with names containing “Diaz”.

Sub SumIfCustomerNameContains()     Dim sumRange As Range     Dim criteriaRange As Range     Dim criteria As String         Set sumRange = Range("F2:F11")     Set criteriaRange = Range("C2:C11")     criteria = Range("I4").Value     Range("J4") = WorksheetFunction.SumIfs(sumRange, criteriaRange, "*" & criteria & "*") End Sub

Notice that we use the “*” wildcard at the beginning and end of the criteria. It will match the customer names that contain “Diaz” in any position: beginning, middle, or end.

Excel VBA SUMIFS between dates

The following SUMIFS VBA procedure sums the Total Order for orders made between October 20, 2021, and November 15, 2021.

Sub SumIfBetweenDates()     Dim sumRange As Range     Dim criteriaRange As Range     Dim criteria1, criteria2 As String         Set sumRange = Range("G2:G11")     Set criteriaRange = Range("B2:B11")         criteria1 = "10/20/2021"     criteria2 = "11/15/2021"         Range("I4") = WorksheetFunction.SumIfs(sumRange, _                     criteriaRange, ">=" & criteria1, _                     criteriaRange, "<=" & criteria2) End Sub

To sum values between dates, notice that we use two criteria in our SUMIFS function.

• The first criteria is for order dates that are on or after “10/20/2021”. For this, we use the “greater than or equal to” operator (>=).
• The second criteria is for order dates that are on or before “11/15/2021”. And for this, we use the “less than or equal to” operator (<=).

Excel VBA SUMIFS for the current month

Now, assume this month is November 2021, and you want to calculate the total discounts for orders placed this month. The solution is similar to the previous SUMIFS between dates example. You just need to change the date criteria with the start and end date of the current month.

Sub SumIfOrdersAreInCurrentMonth()     Dim sumRange As Range     Dim criteriaRange As Range     Dim criteria1, criteria2 As String         Set sumRange = Range("G2:G11")     Set criteriaRange = Range("B2:B11")         criteria1 = DateSerial(Year(Date), Month(Date), 1)     criteria2 = DateSerial(Year(Date), Month(Date) + 1, 0)         Range("I4") = WorksheetFunction.SumIfs(sumRange, _                     criteriaRange, ">=" & criteria1, _                     criteriaRange, "<=" & criteria2) End Sub

In the above sub procedure, we use the following VBA functions to get the first and end date of the current month:

• DateSerial(Year(Date), Month(Date), 1) — returns “11/1/2021”, which is the first day of the current month.
• DateSerial(Year(Date), Month(Date) + 1, 0) — returns “11/30/2021”, which is the last date of the current month.

How to combine SUMIFS with other functions in VBA

SUMIFS can be combined with other functions in VBA. For example, you can combine SUMIFS with VLOOKUP or SUM for more complex scenarios. Take a look at the following examples.

Excel VBA SUMIFS + VLOOKUP

Suppose you have the following tables and want to sum up the total of wholesale orders for “Shining Rose”. You want to do the calculation using VBA and put the result in J6.

However, the Orders table does not have a Product Name, but instead it has a Product Number column. So, you need to get the Product Number of “Shining Rose” from the Products table first, then use it as one of the criteria to find the result from the Orders table using SUMIFS.

Here’s an example solution in VBA. Notice that we use WorksheetFunction.VLookup inside the SUMIFS function.

Sub SumIfsWithVLookup()     Dim sumRange As Range     Dim criteriaRange1, criteriaRange2 As Range     Dim vlRange As Range     Dim vlValue As String         Set sumRange = Range("D3:D14")     Set criteriaRange1 = Range("B3:B14")     Set criteriaRange2 = Range("C3:C14")         ' VLOOKUP range and value     Set xRange = Range("F3:G5")     vlValue = Range("J2").Value         Range("J6") = WorksheetFunction.SumIfs(sumRange, _                     criteriaRange1, Range("J3").Value, _                     criteriaRange2, WorksheetFunction.VLookup(vlValue, vlRange, 2, False))     End Sub

Result:

Excel VBA SUMIFS + SUM across multiple sheets

Suppose you have identical ranges in separate worksheets and want to summarize the order total by customer name in the first worksheet, as shown in the following image:

Here’s an example solution using VBA:

Sub GetOrderTotalByCustomerName() Dim criteria As String Dim result As Double Dim ws As Worksheet ' Activating the first worksheet Worksheets("Summary").Activate ' Selecting the first customer name Range("A2").Select ' Loop for each customer name Do Until ActiveCell.Value = "" criteria = ActiveCell.Value result = 0 ' Loop for each worksheet other than the Summary worksheet For Each ws In Worksheets If ws.Name <> "Summary" Then result = WorksheetFunction.Sum(result, _ WorksheetFunction.SumIfs(ws.Range("C2:C6"), ws.Range("A2:A6"), criteria)) End If Next ws ActiveCell.Offset(0, 1) = result ActiveCell.Offset(1, 0).Select Loop End Sub

Code explanation:

• First, the code activates the Summary worksheet and selects the first customer name in cell A2.
• Then, it loops for each customer from A2, A3, … until the value in the cell is blank. Inside the loop, there’s another loop that iterates through each worksheet and performs a sum of SUMIFS.
• For each customer, their total order is output in column B.

After executing the procedure from VBE, here’s the result:

Bonus: SUMIFS VBA with array arguments for multiple OR criteria not working + solution

With the following table, suppose you want to sum the total order made by either Thomas Walker OR Tamara Chen.

When using a formula in a cell, you can simply use the following combination of SUM and SUMIFS with an array in its criteria argument:

=SUM(SUMIFS(G2:G11,C2:C11, {"Thomas Walker","Tamara Chen"}))

However, the following VBA code will not work:

Worksheetfunction.Sum(Worksheetfunction.SumIfs(Range("G2:G11"), Range("C2:C11"), {"Thomas Walker","Tamara Chen"}))

One of the solutions is to sum up the SUMIFS results for each criteria one by one in a loop, as follows:

Sub SumIfsMultipleOR()     Dim sumRange As Range     Dim criteriaRange As Range     Dim result As Double     Dim i As Integer         Set sumRange = Range("G2:G11")     Set criteriaRange = Range("C2:C11")         Dim criteria As Variant     criteria = Array("Thomas Walker", "Tamara Chen")     For i = 0 To UBound(criteria)         result = WorksheetFunction.Sum(result, _                     WorksheetFunction.SumIfs(sumRange, criteriaRange, criteria(i)))     Next i         Range("I4") = result End Sub

Result:

Wrapping up – SUMIFS VBA

The SUMIFS function is one of the most powerful functions in Excel. It can be used for summing up values based on multiple criteria, which makes it a valuable tool for data analysis and reporting. We’ve covered various examples of writing SUMIFS in VBA, including the syntax it uses, parameters, and examples of how to use it with other functions in VBA.

Interested in learning how to import data from multiple sources into Excel with Coupler.io? Check out the Microsoft Excel integration Coupler.io offers today, as well as how this tool can help you save time and eliminate repetitive work.

Try Coupler.io for free

Fitrianingrum Seto

Software engineer in DevStack with a strong interest in data and writing about it. For over 15 years in software engineering, I have experience in developing SaaS products, data analysis, and technical content writing. The latter is my particular passion since I love producing content that adds value to businesses and helps readers understand complex things.