# Excel SUMIFS VBA: How to Write + Examples

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?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, 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

for each variable just before they go out of scope.**Set** variable = Nothing

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.

Back to Blog