WPS Spreadsheet: Sum Multiple Criteria with One SUMIFS Formula
Question details
The user needs to sum values matching several criteria in the same criteria range with one SUMIFS formula.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Values in column A must be matched against the criteria 322, 325, and 326, while the corresponding values in column F are totaled.
- Observed behavior
- A SUMIFS formula works with one criterion but returns zero when multiple criteria are entered directly in the same criteria range.
Confirm that the criteria values 322, 325, and 326 use the same number or text format as the entries in column A. Also identify the sum range in column F and the matching criteria range in column A before entering the formula.
Use an array constant inside SUMIFS and wrap it in SUM
Use this method when several exact criteria must be evaluated against one criteria range and combined into one total.
SUMIFS returns one result for each value in the array constant. Wrapping the formula in SUM adds those separate results together instead of treating the criteria as one combined condition.
Open the WPS Spreadsheet file, select the cell where you want the combined total to appear, and click the formula bar.
Enter =SUM(SUMIFS(Sheet1!F:F,Sheet1!A:A,{322,325,326})) and replace Sheet1 with the correct worksheet name if needed.
Press Enter to calculate the total. WPS Spreadsheet evaluates SUMIFS for 322, 325, and 326 separately, then SUM adds the three results.
Review the matching rows in column A and confirm that their corresponding values in column F are included in the displayed total.
Calculate Multi-Criteria SUMIFS Totals in WPS Spreadsheet
WPS Spreadsheet lets you combine SUMIFS with an array constant and SUM to calculate one total for multiple exact criteria. The familiar formula interface also supports Microsoft Excel workbooks for easy migration.
- 1. Open your workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the data.
- 2. Choose the output cell: Click the cell where the multi-criteria total should appear, then select the formula bar.
- 3. Enter the formula: Type =SUM(SUMIFS(Sheet1!F:F,Sheet1!A:A,{322,325,326})) and adjust the sheet name or ranges if required.
- 4. Confirm and verify: Press Enter, then compare the result with the rows whose column A values are 322, 325, or 326.
Frequently Asked Questions
Why does SUMIFS return zero when I enter several criteria in one criteria range?
SUMIFS normally expects one criteria argument for each criteria range. Entering several values directly may not combine them as an OR condition. Use an array constant inside SUMIFS and wrap the formula in SUM, as in =SUM(SUMIFS(Sheet1!F:F,Sheet1!A:A,{322,325,326})).
How do I add more criteria to the formula?
Add each exact value inside the array constant, separated by commas. For example, use {322,325,326,327} to include four criteria in the same column A range.
Can I use cell references instead of typing 322, 325, and 326?
Yes. Replace the array constant with a range of criteria when supported by your WPS Spreadsheet version, or add separate SUMIFS results for referenced cells and combine them with SUM. Ensure the referenced criteria use the same data type as column A.
What does the first range in the SUMIFS formula represent?
The first range, Sheet1!F:F, is the sum range. WPS Spreadsheet adds values from this range when the corresponding cells in Sheet1!A:A match 322, 325, or 326.
Can this formula be used in an Excel workbook?
Yes. The SUMIFS and SUM functions and this array-constant pattern are commonly supported in Microsoft Excel XLSX files. After opening the workbook in WPS Spreadsheet, verify the sheet name, ranges, and calculated result.
