Excel VBA

Excel VBA SUMIFS: Syntax, Examples, Dates, and Current Month

For a VBA SUMIFS example, call WorksheetFunction.SumIfs when you want a normal Double result, or Application.SumIfs when you want a Variant that can be checked with IsError. The copyable examples below show SUMIFS in VBA with text criteria, multiple criteria, DateSerial date criteria, and current-month boundaries.

Key point

Call SUMIFS through Excel objects in VBA: sum_range first, then criteria_range and criteria pairs. Use DateSerial plus numeric date boundaries for date criteria instead of locale-specific typed date strings.

VBA SUMIFS Code Builder

Choose a VBA call style and SUMIFS pattern, then copy the macro, the SUMIFS call, or a plain-language summary.

Complete VBA macro

Sub BuildVbaSumIfsCode()
    Dim ws As Worksheet
    Dim result As Variant

    Set ws = ThisWorkbook.Worksheets("Sales")

    result = Application.SumIfs( _
        ws.Range("D2:D100"), _
        ws.Range("B2:B100"), _
        "East", _
        ws.Range("C2:C100"), _
        "Widget" _
    )

    If IsError(result) Then
        ws.Range("G2").Value = "Check SUMIFS ranges or criteria"
    Else
        ws.Range("G2").Value = result
    End If
End Sub

SUMIFS call

Application.SumIfs( _
        ws.Range("D2:D100"), _
        ws.Range("B2:B100"), _
        "East", _
        ws.Range("C2:C100"), _
        "Widget" _
    )

What this macro does

This macro sums D2:D100 on Sales where B2:B100 equals 'East' and C2:C100 equals 'Widget', then writes the result to G2. It checks IsError before writing the result.

Ready-to-use VBA examples

Basic WorksheetFunction.SumIfs example

Sub BasicSumIfsExample()
    Dim ws As Worksheet
    Dim total As Double

    Set ws = ThisWorkbook.Worksheets("Sales")

    total = Application.WorksheetFunction.SumIfs( _
        ws.Range("D2:D100"), _
        ws.Range("B2:B100"), "East", _
        ws.Range("C2:C100"), "Widget" _
    )

    ws.Range("G2").Value = total
End Sub

With the sample data on this page, East + Widget returns 420. Keep the sum range and criteria ranges on the same rows.

Application.SumIfs with error check

Sub SumIfsWithApplicationErrorCheck()
    Dim ws As Worksheet
    Dim result As Variant

    Set ws = ThisWorkbook.Worksheets("Sales")

    result = Application.SumIfs( _
        ws.Range("D2:D100"), _
        ws.Range("B2:B100"), "East", _
        ws.Range("C2:C100"), "Widget" _
    )

    If IsError(result) Then
        ws.Range("G2").Value = "Check SUMIFS ranges or criteria"
    Else
        ws.Range("G2").Value = result
    End If
End Sub

Use Application.SumIfs when user-edited ranges should not stop the whole macro with a runtime error.

String and wildcard criteria

Dim keyword As String
keyword = "Wid"

total = Application.WorksheetFunction.SumIfs( _
    ws.Range("D2:D100"), _
    ws.Range("B2:B100"), "East", _
    ws.Range("C2:C100"), "*" & keyword & "*" _
)

Use quoted strings for exact criteria and concatenate wildcards when the match text comes from a variable.

Current month DateSerial macro

firstDay = DateSerial(Year(Date), Month(Date), 1)
nextMonth = DateSerial(Year(Date), Month(Date) + 1, 1)

total = Application.WorksheetFunction.SumIfs( _
    ws.Range("D2:D100"), _
    ws.Range("A2:A100"), ">=" & CDbl(firstDay), _
    ws.Range("A2:A100"), "<" & CDbl(nextMonth) _
)

Use >= firstDay and < nextMonth so month-end rows with times are included.

Choose the VBA SUMIFS call

Use Application.WorksheetFunction.SumIfs for a compact VBA SUMIFS example when invalid arguments should raise a runtime error during testing.

Use Application.SumIfs when user-edited ranges may be wrong and the macro should test IsError(result) instead of stopping.

In both versions, the sum range comes first, then criteria range and criteria pairs. With the sample data, Region = East and Product = Widget returns 420.

This page is for Excel VBA. Google Sheets does not support VBA, so the Sheets equivalent is a normal SUMIFS formula or an Apps Script function.

VBA SUMIFS syntax

Excel VBA does not have a separate language-level SUMIFS command. Call Excel's worksheet function through Application.WorksheetFunction.SumIfs or Application.SumIfs.

The argument order is sum_range first, followed by criteria_range and criteria pairs. A common VBA error is putting the first criteria range before the sum range.

VBA SUMIFS signature

Application.WorksheetFunction.SumIfs(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Aligned range example

total = Application.WorksheetFunction.SumIfs(ws.Range("D2:D100"), ws.Range("B2:B100"), "East", ws.Range("C2:C100"), "Widget")

Application.SumIfs vs WorksheetFunction.SumIfs

WorksheetFunction.SumIfs returns a Double when the call is valid, but invalid ranges or arguments can raise a runtime error.

Application.SumIfs returns a Variant. That makes it easier to test IsError(result) and show a controlled message instead of stopping a macro that other users run.

Application.SumIfs with error check

result = Application.SumIfs(ws.Range("D2:D100"), ws.Range("B2:B100"), "East", ws.Range("C2:C100"), "Widget")
If IsError(result) Then
    ws.Range("G2").Value = "Check SUMIFS ranges or criteria"
Else
    ws.Range("G2").Value = result
End If

Text criteria and multiple criteria

Text criteria such as "East" and "Widget" are passed as normal VBA strings.

Most VBA SUMIFS examples use more than one condition. Add another criteria range and criteria value for each additional requirement.

Use fully qualified worksheet ranges so the macro does not accidentally read from the active sheet.

Text criteria: Region = East

total = Application.WorksheetFunction.SumIfs(ws.Range("D2:D100"), ws.Range("B2:B100"), "East")

Expected output with the sample data: 1335.

Multiple criteria: Region and product

total = Application.WorksheetFunction.SumIfs(ws.Range("D2:D100"), ws.Range("B2:B100"), "East", ws.Range("C2:C100"), "Widget")

Expected output with the sample data: 420.

String criteria variables

regionName = "East"
productName = "Widget"
total = Application.WorksheetFunction.SumIfs( _
    ws.Range("D2:D100"), _
    ws.Range("B2:B100"), regionName, _
    ws.Range("C2:C100"), productName _
)

Expected output with the sample data: 420.

String criteria operators in VBA SUMIFS

String criteria in VBA SUMIFS can use the same operators and wildcards as worksheet SUMIFS. Put the operator inside the criteria string, then concatenate a variable when the value comes from a cell or prompt.

Use exact text for a normal match, <> for not equal, and asterisks for contains logic. Keep quotes inside the VBA string and qualify every range with the worksheet object.

Not equal string criteria

total = Application.WorksheetFunction.SumIfs(ws.Range("D2:D100"), ws.Range("E2:E100"), "<>Pending")

Expected output with the sample data: 1370 for rows whose Status is not Pending.

Contains text wildcard

total = Application.WorksheetFunction.SumIfs(ws.Range("D2:D100"), ws.Range("C2:C100"), "*Wid*")

Expected output with the sample data: 1370 for Widget rows.

String operator with variable

statusName = "Paid"
total = Application.WorksheetFunction.SumIfs( _
    ws.Range("D2:D100"), _
    ws.Range("E2:E100"), "=" & statusName _
)

Expected output with the sample data: 1370 for Paid rows.

Detailed guide

Need the assumptions, examples, and troubleshooting?

The calculator stays focused here. The supporting reference has moved to its own page.

Read the Excel VBA SUMIFS: Syntax, Examples, Dates, and Current Month guide