Excel VBA SUMIFS: Syntax, Examples, Dates, and C Guide

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.

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

This macro writes the current-month total to G2 and uses the first day of next month as an exclusive boundary, so month-end time values are included.

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.

Keep every active range 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

Official references

WorksheetFunction.SumIfs method from Microsoft
DateSerial function from Microsoft
Range.Formula property from Microsoft

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.