VBA & Macro Problems

Fix Excel VBA SUMIFS Date Criteria Type Mismatch

Rana GarciaRana Garcia Nov 18, 2026 868 views

Question details

The user needs to build an Excel VBA SUMIFS formula with valid date criteria without triggering a type mismatch.

Fix Excel VBA SUMIFS Date Criteria Type Mismatch
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A macro inserts a date variable into a SUMIFS formula to total values for a month.
Observed behavior
The formula or macro can produce a type mismatch when the date variable is inserted incorrectly or quotation marks are not escaped properly.
Before you start

Check that the source date is valid and that the dates in column A are stored as Excel dates rather than text. In the example below, D1 contains the reference date, column A contains dates, and column B contains values to sum.

Solution 1Recommended

Build the SUMIFS formula with escaped quotes and date components

Use this approach when a VBA macro must insert a monthly SUMIFS formula into a worksheet.

The formula uses DATE with numeric year and month components instead of inserting a date as formatted text. Its end criterion is earlier than the first day of the next month, so it also includes dates with time values on the last day.

1
Open the VBA editor

In Excel, open Developer > Visual Basic, then open the module containing your macro.

2
Validate the reference date

Before building the formula, validate the source and assign it to a Date variable. For example: Dim prevDate As Date If Not IsDate(ActiveSheet.Range("D1").Value) Then MsgBox "Enter a valid date in D1." Exit Sub End If prevDate = CDate(ActiveSheet.Range("D1").Value)

3
Assign the formula

In the same macro, write the formula to the result cell. Double quotes inside the VBA string so Excel receives the criteria correctly: ActiveSheet.Range("E1").Formula = "=SUMIFS(B:B,A:A,"">=""&DATE(" & Year(prevDate) & "," & Month(prevDate) & ",1),A:A,""<""&DATE(" & Year(prevDate) & "," & Month(prevDate) & "+1,1))"

4
Run and check the result

Run the macro from Developer > Macros, then select E1 and check the formula bar and calculated total. Adjust D1, E1, and the column references to match your worksheet.

Build the SUMIFS formula with escaped quotes and date components
Check the date column: SUMIFS compares Excel date values. Dates stored as text in column A may need to be converted to real dates before the result is correct.
Free Microsoft Office alternative

Work with date-based spreadsheets in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar interface for opening and editing spreadsheets. Its Excel format support makes it easy to move everyday workbook tasks, including date-based totals, to WPS Spreadsheets. It does not fix Microsoft Excel VBA errors, and macro compatibility may vary.

Use spreadsheet formulas to work with date-based totalsOpen and edit Microsoft Excel XLSX and XLS filesNavigate a familiar, easy-to-use spreadsheet interfaceMove everyday workbook editing to a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why do I need double quotation marks in the VBA formula string?

VBA uses quotation marks to delimit strings. Write two consecutive quotation marks inside the VBA string to place one quotation mark in the resulting Excel formula, such as the quotes around ">=".

How can I check whether the reference date is valid?

Use IsDate on the source value before assigning it to a Date variable. For example, check IsDate(Range("D1").Value), then use CDate only if the check succeeds.

Will the formula include dates with times on the last day of the month?

Yes. The example uses a criterion earlier than the first day of the next month, so date-time values throughout the last day are included.

Why is SUMIFS returning zero even though the month has matching rows?

Check whether the entries in column A are real Excel dates rather than text, and confirm that column B contains numeric values. Also check the formula bar to verify the criteria and range references.