Formula Errors

Fix an Excel SUMIFS Date Range Syntax Error

Camila MilosovichCamila Milosovich Nov 12, 2026 869 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the comma-based formula

Select the result cell and enter =SUMIFS($H$2:$H$300,$E$2:$E$300,">"&M2,$E$2:$E$300,"<"&M3), then press Enter.

2
Replace commas if needed

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

3
Check the date boundaries

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.

Sorting is not required: SUMIFS can evaluate the date criteria without sorting the source data. Remove unrelated arguments such as TRUE or 0 if they are not part of the function syntax.
WPS Spreadsheets solution

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. 1. Open the spreadsheet: Launch WPS Office, choose Spreadsheets, and open the workbook containing the amount, date, and boundary-date columns.
  2. 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. 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. 4. Verify the result: Check that column E contains valid dates, M2 is earlier than M3, and column H contains the values to total.
Resolve SUMIFS date-range syntax errors caused by comma or semicolon settingsUse paired date criteria to total values between two worksheet datesWork in a lightweight spreadsheet interface with familiar formula toolsOpen and save Microsoft Excel .xlsx files with broad format compatibility
microsoft office alternative - wps office

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.