Fix an Excel SUMIFS Date Range Syntax Error
Question details
The user needs to correct a SUMIFS formula that returns a syntax error when filtering values between two dates.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A SUMIFS formula is being used to total values in H2:H300 where dates in E2:E300 fall between the dates in M2 and M3.
- Observed behavior
- The SUMIFS formula produces a syntax error, while a similar SUMIF formula works. The regional list-separator setting may not match the separators in the formula.
Check whether your spreadsheet uses commas or semicolons between function arguments by looking at a working formula in the same file. Confirm that M2 and M3 contain valid dates and that column E contains comparable date values.
Use the list separator required by your regional settings
Use this solution when SUMIFS shows a syntax error even though the ranges and date criteria are logically correct.
SUMIFS requires each criteria range to be paired with its criteria. The formula does not require sorted data, but its argument separators must match your regional settings.
Select the result cell and enter =SUMIFS($H$2:$H$300,$E$2:$E$300,">"&M2,$E$2:$E$300,"<"&M3), then press Enter.
If Excel rejects the comma-based formula, edit the formula in the formula bar and replace every comma between arguments with a semicolon: =SUMIFS($H$2:$H$300;$E$2:$E$300;">"&M2;$E$2:$E$300;"<"&M3).
Verify that M2 is the lower boundary and M3 is the upper boundary, and confirm that the comparison operators in the criteria are paired with the cell references using the & operator.
Calculate date-based totals with SUMIFS in WPS Spreadsheets
WPS Spreadsheets can create and calculate the same SUMIFS date-range formula. Enter the formula with the separator used by your regional settings, then save or share the workbook in Excel-compatible format.
- 1. Open the spreadsheet: Launch WPS Office, choose Spreadsheets, and open the workbook containing the amount, date, and boundary-date columns.
- 2. Select the result cell: Click the cell where the total should appear and enter =SUMIFS($H$2:$H$300,$E$2:$E$300,">"&M2,$E$2:$E$300,"<"&M3).
- 3. Match the regional separator: If WPS Spreadsheets displays a syntax error, replace the commas with semicolons throughout the formula and press Enter again.
- 4. Verify the result: Check that column E contains valid dates, M2 is earlier than M3, and column H contains the values to total.

Frequently Asked Questions
Why does SUMIFS show a syntax error when SUMIF works?
SUMIFS has multiple range-and-criteria pairs, so the formula is more sensitive to argument separators. Use commas or semicolons according to the spreadsheet's regional settings.
What is the correct comma-based SUMIFS date formula?
Use =SUMIFS($H$2:$H$300,$E$2:$E$300,">"&M2,$E$2:$E$300,"<"&M3) when your regional settings use commas between function arguments.
How do I write the formula when my region uses semicolons?
Replace each comma with a semicolon: =SUMIFS($H$2:$H$300;$E$2:$E$300;">"&M2;$E$2:$E$300;"<"&M3).
Does SUMIFS require the date data to be sorted?
No. SUMIFS does not require sorting. The source dates only need to be valid date values that can be compared with M2 and M3.




