Fix Excel SUM and XLOOKUP Formulas for Regional Settings in WPS Office
Question details
The user needs to correct SUM and XLOOKUP formulas that fail on European Excel installations because of list separator differences, invalid range construction, or regional date settings.
- Product
- WPS Office Spreadsheets (Microsoft Excel compatible)
- Device & OS
- not provided
- Scenario
- The user is entering or repairing a formula that sums or looks up values between two dates in a spreadsheet that uses European regional settings.
- Observed behavior
- The SUM and XLOOKUP formula returns an error or incorrect result when commas are used instead of semicolons, or when XLOOKUP is used to join two lookup results as an invalid range.
Before editing the formula, confirm whether your installation requires commas or semicolons as the list separator, and verify that each lookup array and return array has the same number of rows or columns.
Sum all values between two dates with FILTER and LET instead of joined XLOOKUP results
Use this approach when the goal is to total all rows that fall within a date range, rather than adding two individual lookup values.
FILTER returns all rows that meet both date conditions. LET names that filtered range so SUM can total the returned values cleanly. This avoids the invalid range construction that can occur when you join two XLOOKUP results.
Click the cell where the total should appear, then type =LET(filtered,FILTER($R$5:$S$11,(Datum>=U5)*(Datum<=V5),0),SUM(filtered)).
Replace $R$5:$S$11 with your actual return range, Datum with your date range, and U5/V5 with the cells that hold the start and end dates.
If your local installation uses semicolons as the list separator, change every comma in the formula to a semicolon.
Press Enter and confirm that the result includes all values within the two dates.
Use SUMPRODUCT to sum values between two dates when FILTER is not available
Use this alternative on older spreadsheet versions that do not support dynamic array functions.
Add two individual XLOOKUP results when you only need to sum two lookup values
Use this when you intend to add two lookup results rather than sum a whole filtered range.
Fix SUM and XLOOKUP regional formula errors in WPS Spreadsheets
Use WPS Spreadsheets to open your Excel workbook, adjust list separators, and apply a criteria-based FILTER, SUMPRODUCT, or XLOOKUP formula that totals the correct values.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the Excel workbook that contains the failing formula.
- 2. Select the formula cell: Click the cell with the invalid SUM/XLOOKUP formula, or click the result cell where you want the corrected formula.
- 3. Apply a criteria-based formula: Enter the FILTER/LET formula or SUMPRODUCT formula shown above, replacing cell references with your actual data.
- 4. Adjust commas or semicolons: Change the formula list separators to match your system regional settings, then press Enter.
- 5. Save the workbook: Click Menu > Save As and keep the .xlsx format if you need to share with Excel users.
Frequently Asked Questions
Why does my Excel SUM and XLOOKUP formula fail on a European system?
Most often the list separator is a semicolon instead of a comma. Open the formula, replace commas with semicolons, and ensure your ranges use the required delimiter.
Should I join two XLOOKUP results to sum all values between two dates?
No. Joining two XLOOKUP results can create an invalid range. Use a criteria-based formula such as FILTER with SUM or SUMPRODUCT to return all matching values.
How do I know whether to use commas or semicolons in WPS Spreadsheets?
Check your operating system regional format settings. In many European locales the list separator is a semicolon; in US English it is usually a comma. You can also look at existing formulas in the workbook or type =SUM and observe the suggested separator.
Can I still use =SUM(XLOOKUP(...),XLOOKUP(...))?
Yes, if your intention is to add two individual lookup results. Put each XLOOKUP inside the SUM function, use matching array dimensions, and separate them with the correct list separator.
Does WPS Spreadsheets support FILTER and LET formulas?
WPS Office supports dynamic array functions in current versions. If your version does not offer FILTER or LET, use SUMPRODUCT as a compatible alternative.
