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.
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?ematches “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, take a look at Coupler.io. It’s a solution that allows you to import data from different sources such as Shopify, Jira, Airtable, etc., into Excel, Google Sheets, or BigQuery.
Now, let’s see a basic example of writing Excel SUMIFS in VBA. You can download the following file to follow along:
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:
- 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.
- On the menu, click Insert > Module.
- 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.
- Run the code by pressing F5.
- 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.
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:
- Click the Developer tab, then click Insert > Button (Form Control).
- Click and drag anywhere on the worksheet to create a button.
- In the “Assign Macro” dialog, select
SumOrderTotalByDiscountUsingVariables, then click OK.
- If you want, click the button’s text and edit it to “Calculate” to give it a more descriptive name.
- 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 different 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.
