Fix Excel VBA SUMIFS Date Criteria Type Mismatch
Question details
The user needs to build an Excel VBA SUMIFS formula with valid date criteria without triggering a 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.
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.
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.
In Excel, open Developer > Visual Basic, then open the module containing your macro.
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)
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))"
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.

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.

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.




