Excel VBA workflow

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

Q: How do I use SUMIFS in VBA?

Use Application.WorksheetFunction.SumIfs or Application.SumIfs. Put the sum range first, then add each criteria range and criteria value pair.

Q: What is the syntax for WorksheetFunction.SumIfs?

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

Q: Should I use WorksheetFunction.SumIfs or Application.SumIfs?

Use WorksheetFunction.SumIfs when a runtime error is acceptable during development. Use Application.SumIfs when you want to test IsError(result) and keep the macro running.

Q: Why does VBA SUMIFS return 0 with dates?

The source date may include a time value or the criteria may be passed as locale-specific text. Build boundaries with DateSerial and compare >= start and < next boundary.

Q: How do I write SUMIFS between two dates in VBA?

Create startDate and nextDate with DateSerial, then use ">=" & CDbl(startDate) and "<" & CDbl(nextDate) as criteria.

Q: How do I sum the current month with VBA SUMIFS?

Use DateSerial(Year(Date), Month(Date), 1) for the first day and DateSerial(Year(Date), Month(Date) + 1, 1) for the first day of next month.

Q: Do sum_range and criteria_range need to be the same size?

Excel can evaluate some offset ranges, but examples should use the same start row and end row to avoid wrong rows and hard-to-debug results.

Q: Can VBA SUMIFS use multiple criteria?

Yes. Add each additional criteria range and criteria value after the previous pair, such as Region and Product or Region and Status.

Q: Can VBA SUMIFS use OR criteria?

SUMIFS handles AND criteria directly. For OR criteria in VBA, loop over the allowed values and add each SUMIFS result.

Q: Can I use SUMIFS with Excel Tables in VBA?

Yes. Use a ListObject and pass DataBodyRange from the table's ListColumns into SUMIFS.

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.

Copy-paste formulas

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.

Quick VBA SUMIFS patterns

Use these four patterns as starting points before you adapt the ranges to your workbook.

All examples are Excel VBA only. Google Sheets does not run VBA; use Google Apps Script or a worksheet formula there instead.

Basic example

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

Returns 420 from the sample rows when Region is East and Product is Widget.

Application.SumIfs with error handling

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

String wildcard criteria

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

Use this for contains logic, such as product names that contain the keyword.

Current month date range

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))

Uses >= the first day and < the first day of next month.

Direct answer for sumifs in VBA

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.

VBA SUMIFS date criteria

For date criteria, build real date boundaries with DateSerial and pass Excel serial values into the criteria string. Avoid typed date strings such as >=1/1/2026 because they can be interpreted differently by locale.

Use CDbl(DateSerial(...)) in the examples so readers understand that Excel dates are serial numbers. Do not use CLng to truncate arbitrary date-time values; create clean midnight boundaries with DateSerial instead.

Date criteria boundary

Dim startDate As Date
startDate = DateSerial(2026, 1, 1)
total = Application.WorksheetFunction.SumIfs( _
    ws.Range("D2:D100"), _
    ws.Range("A2:A100"), ">=" & CDbl(startDate), _
    ws.Range("B2:B100"), "East" _
)

Expected output with the sample data: 695 for January East rows.

SUMIFS between two dates in VBA

Use a lower boundary and an exclusive upper boundary when a date column can contain time values. The example below totals January rows by using >= January 1 and < February 1.

This is safer than <= January 31 because a value such as 2026-01-31 15:30 is greater than midnight on January 31.

Between two dates with DateSerial

Dim startDate As Date, nextDate As Date
startDate = DateSerial(2026, 1, 1)
nextDate = DateSerial(2026, 2, 1)
total = Application.WorksheetFunction.SumIfs( _
    ws.Range("D2:D100"), _
    ws.Range("A2:A100"), ">=" & CDbl(startDate), _
    ws.Range("A2:A100"), "<" & CDbl(nextDate) _
)

Expected output with the sample data: 1005 for all January rows.

When VBA SUMIFS returns zero for date-time rows

If the date column includes times, a criterion that equals one date can miss every row because 2026-01-15 14:30 is greater than midnight on 2026-01-15.

Use a lower boundary at midnight and an exclusive upper boundary at the next day or next month. This is the same reason the current-month macro uses < nextMonth instead of <= lastDay.

One day with date-time values

targetDay = DateSerial(Year(vDate), Month(vDate), Day(vDate))
nextDay = targetDay + 1
total = Application.WorksheetFunction.SumIfs( _
    ws.Range("D2:D100"), _
    ws.Range("A2:A100"), ">=" & CDbl(targetDay), _
    ws.Range("A2:A100"), "<" & CDbl(nextDay) _
)

Use this pattern when a date column stores real date-time values.

VBA SUMIFS current month macro

This macro writes the current-month total to G2 on the Sales worksheet.

Complete macro

Sub SumCurrentMonth()
    Dim ws As Worksheet
    Dim firstDay As Date
    Dim nextMonth As Date
    Dim total As Double

    Set ws = ThisWorkbook.Worksheets("Sales")
    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) _
    )

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

The current-month macro is now a practical date example, not the only purpose of the page.

How the DateSerial month boundary works

DateSerial(Year(Date), Month(Date), 1) creates the first day of the current month.

DateSerial(Year(Date), Month(Date) + 1, 1) creates the first day of next month.

Use < nextMonth instead of <= lastDay for timestamp-safe comparisons.

CDbl(date) makes the date criteria explicit as Excel serial values. Use DateSerial to create clean boundaries before converting.

Sum current month with another criterion

Add another criteria range and value, such as Region = East, after the date criteria.

Current month and region

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

Expected output depends on the current month. Use this pattern for Region, Status, Product, or similar criteria.

VBA SUMIFS with Excel Tables / ListObjects

If the source is an Excel Table, use ListObject column ranges instead of entire worksheet columns. This keeps the macro easier to audit when rows are added.

The example assumes a table named SalesTable with columns Amount, Region, and Product.

ListObject SUMIFS

Dim tbl As ListObject
Set tbl = ws.ListObjects("SalesTable")

total = Application.WorksheetFunction.SumIfs( _
    tbl.ListColumns("Amount").DataBodyRange, _
    tbl.ListColumns("Region").DataBodyRange, "East", _
    tbl.ListColumns("Product").DataBodyRange, "Widget" _
)

OR criteria in VBA SUMIFS

SUMIFS handles AND logic naturally. For OR logic, run SUMIFS once for each allowed value and add the results, or use a worksheet formula outside VBA.

Do not pass a worksheet array constant directly as a normal VBA SUMIFS criterion unless you are deliberately using Evaluate and have tested the result.

OR criteria loop

Dim regions As Variant
Dim item As Variant

regions = Array("East", "West")
total = 0

For Each item In regions
    total = total + Application.WorksheetFunction.SumIfs( _
        ws.Range("D2:D100"), _
        ws.Range("B2:B100"), CStr(item), _
        ws.Range("C2:C100"), "Widget" _
    )
Next item

Expected output with the sample data: East Widget plus West Widget rows.

Write the result to a worksheet cell

Assign the total to a cell such as ws.Range("G2").Value after the SumIfs call finishes.

Write output

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

Write a SUMIFS formula into a cell with .Formula

Use .Formula when the macro should place a worksheet formula in the sheet instead of calculating the total inside VBA.

Double every quote that belongs inside the worksheet formula, and build wildcard or threshold criteria before assigning the final string.

Runtime error 1004 usually means the formula string is invalid, the worksheet range is not qualified, or the formula uses local separators that belong in .FormulaLocal instead of .Formula.

Write fixed text and wildcard criteria

Sub WriteSumifsFormula()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sales")

    ws.Range("F1").Value = "Wid"
    ws.Range("G2").Formula = "=SUMIFS($D$2:$D$100,$B$2:$B$100,""East"",$C$2:$C$100,""*""&$F$1&""*"")"
End Sub

The doubled quotes produce criteria such as "East" and "*"&$F$1&"*" inside the worksheet formula.

Write date boundaries into a formula

ws.Range("G3").Formula = "=SUMIFS($D$2:$D$100,$A$2:$A$100,"">=""&DATE(2026,1,1),$A$2:$A$100,""<""&DATE(2026,2,1))"

Use US-style function names and comma separators with .Formula.

Common VBA SUMIFS date errors

WorksheetFunction.SumIfs can raise an error if ranges are invalid.

Application.SumIfs may be easier to handle when you want to avoid runtime errors.

Recommended examples should keep all criteria ranges aligned with the sum range. Use the same start row and end row, such as D2:D100 with A2:A100 and B2:B100.

A result of zero with a visible matching date often means the source cell contains a time value. Test the start and next-boundary criteria separately.

Runtime error 1004 usually points to invalid range objects, missing worksheet qualification, or unsupported criteria construction.

Wrong rows summed often comes from mixing whole-column ranges such as D:D with bounded ranges such as A2:A100.

Formula alternative without VBA

If you do not need a macro, use a worksheet SUMIFS formula with the same current-month boundaries.

Worksheet formula

=SUMIFS(D2:D100,A2:A100,">="&EOMONTH(TODAY(),-1)+1,A2:A100,"<"&EOMONTH(TODAY(),0)+1)

When not to use VBA for this task

Do not use VBA when a normal SUMIFS formula is enough and the workbook does not already use macros.

Do not use WorksheetFunction.SumIfs without error handling when user-edited ranges may be invalid.

Sample data and returned totals

Date	Region	Product	Amount	Status
2026-01-04 09:15	East	Widget	420	Paid
2026-01-12 16:30	West	Widget	310	Paid
2026-01-25 14:00	East	Gadget	275	Pending
2026-02-03 08:45	East	Widget	640	Paid

WorksheetFunction.SumIfs vs Application.SumIfs

VBA call	Return type	Error behavior	Best for
Application.WorksheetFunction.SumIfs(...)	Double when valid.	Invalid ranges or criteria can raise a runtime error on the SUMIFS line.	Development and controlled workbooks where a hard failure is acceptable.
Application.SumIfs(...)	Variant.	Invalid input can be checked with IsError(result) so the macro can show a message instead of stopping.	User-maintained workbooks where the macro should show a controlled message.

VBA SUMIFS string criteria patterns

Criteria goal	VBA criteria	Use it for
Exact text	"East"	Match one region, status, product, or customer name.
Not equal	"<>Pending"	Exclude one status or label.
Contains variable text	"" & keyword & ""	Match cells that contain a word or fragment from a variable.
Greater than threshold	">" & threshold	Compare amounts, quantities, scores, or other numeric values.
Exact variable text	"=" & statusName	Build criteria from a cell, prompt, or previous macro step.

Returned totals from the VBA examples

VBA pattern	Returned result from the sample rows	Why
Region = East and Product = Widget	420	Only the 2026-01-04 East Widget row matches both criteria.
Region = East only	1335	Adds East rows 420, 275, and 640.
January 2026 date boundary	1005	Adds the three January rows: 420, 310, and 275.
January 2026 date boundary plus Region = East	695	Adds 2026-01-04 East Widget 420 and 2026-01-25 East Gadget 275.
OR criteria loop for East and West Widget	1370	Adds East Widget rows 420 and 640 plus West Widget row 310.

VBA SUMIFS date debug checklist

Symptom	Likely cause	Fix
SUMIFS returns 0 for a visible date	The date column contains times after midnight.	Use >= CDbl(startDate) and < CDbl(nextDay or nextMonth).
Macro stops on the SumIfs line	WorksheetFunction.SumIfs raised a runtime error.	Try Application.SumIfs and test the result with IsError while debugging.
Runtime error 1004	The range object is invalid, the worksheet is not qualified, or a criteria range does not match the sum range shape.	Set ws explicitly, qualify every range with ws.Range(...), and align all ranges.
SUMIFS works on the active sheet only	One or more ranges are unqualified, so VBA reads ActiveSheet.Range instead of the intended worksheet.	Use ws.Range for the sum range and every criteria range.
Wrong total or runtime error from ranges	The sum range and criteria ranges use different row counts or different worksheets.	Use D2:D100 with A2:A100, B2:B100, and C2:C100 on the same worksheet.
Current month total includes last month	The lower boundary is missing or built from a text date.	Use DateSerial(Year(Date), Month(Date), 1).
Current month total misses month-end rows	The upper boundary uses <= lastDay with date-time values.	Use < DateSerial(Year(Date), Month(Date) + 1, 1).
SUMIFS works on the sheet but not in VBA	The criteria was passed as locale-specific date text.	Pass criteria as ">=" & CDbl(firstDay) and "<" & CDbl(nextMonth).
OR criteria does not work	SUMIFS is built for AND logic across criteria pairs.	Loop over allowed values and add each SUMIFS result.

Related tools and guides

FAQ

How do I use SUMIFS in VBA?

Use Application.WorksheetFunction.SumIfs or Application.SumIfs. Put the sum range first, then add each criteria range and criteria value pair.

What is the syntax for WorksheetFunction.SumIfs?

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

Should I use WorksheetFunction.SumIfs or Application.SumIfs?

Use WorksheetFunction.SumIfs when a runtime error is acceptable during development. Use Application.SumIfs when you want to test IsError(result) and keep the macro running.

Why does VBA SUMIFS return 0 with dates?

The source date may include a time value or the criteria may be passed as locale-specific text. Build boundaries with DateSerial and compare >= start and < next boundary.

How do I write SUMIFS between two dates in VBA?

Create startDate and nextDate with DateSerial, then use ">=" & CDbl(startDate) and "<" & CDbl(nextDate) as criteria.

How do I sum the current month with VBA SUMIFS?

Use DateSerial(Year(Date), Month(Date), 1) for the first day and DateSerial(Year(Date), Month(Date) + 1, 1) for the first day of next month.

Do sum_range and criteria_range need to be the same size?

Excel can evaluate some offset ranges, but examples should use the same start row and end row to avoid wrong rows and hard-to-debug results.

Can VBA SUMIFS use multiple criteria?

Yes. Add each additional criteria range and criteria value after the previous pair, such as Region and Product or Region and Status.

Can VBA SUMIFS use OR criteria?

SUMIFS handles AND criteria directly. For OR criteria in VBA, loop over the allowed values and add each SUMIFS result.

Can I use SUMIFS with Excel Tables in VBA?

Yes. Use a ListObject and pass DataBodyRange from the table's ListColumns into SUMIFS.