Simplify SUMIFS for Multiple Products and Prices in WPS Office Spreadsheets
Question details
The user needs a compact Excel/WPS formula to sum prices for one or more products when the price must be 20 or 50, without writing several SUMIFS formulas.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A product and price table contains products in column A and prices in column B, and the user wants totals for selected products with prices of 20 or 50.
- Observed behavior
- Manually adding multiple SUMIFS formulas is repetitive, long, and harder to maintain when criteria change.
Confirm that product names in column A and prices in column B are clean and consistent: no extra spaces, no text-formatted numbers, and no merged cells inside the data range, because SUMIFS criteria must match exactly.
Use SUM(SUMIFS(...)) with array constants for one or more products
Best when you want a single compact formula that totals Product A alone or Products A and B together for prices 20 or 50.
Wrapping SUMIFS in SUM lets WPS Spreadsheets evaluate the array criteria and return one grand total without stacking separate SUMIFS formulas.
Click an empty cell where you want the total to appear in WPS Spreadsheets.
Type =SUM(SUMIFS(B2:B12,A2:A12,"Product A",B2:B12,{20,50})) and press Enter.
Type =SUM(SUMIFS(B2:B12,A2:A12,{"Product A","Product B"},B2:B12,{20,50})) and press Enter to total Products A and B together.
Replace B2:B12, A2:A12, the product names, and {20,50} with your actual data range and target values.
Use an Excel Table and slicers for visual filtering
Use this when you change products or prices often and prefer selecting filters visually instead of editing array constants.
Simplify SUMIFS array formulas in WPS Office Spreadsheets
Use one SUM(SUMIFS(...)) array-constant formula in WPS Office Spreadsheets to total prices for multiple products and price values such as 20 or 50, without stacking multiple SUMIFS formulas.
- 1. Open WPS Spreadsheets: Launch WPS Office and open the .xlsx workbook that contains products in column A and prices in column B.
- 2. Click the target cell: Select an empty cell where you want the compact SUMIFS total to appear.
- 3. Enter the compact formula: Type =SUM(SUMIFS(B2:B12,A2:A12,"Product A",B2:B12,{20,50})) and press Enter, replacing the range and criteria with your data.
- 4. Extend for multiple products: Use {"Product A","Product B"} as the product criteria when you need both products in the same formula.
- 5. Verify the total: Compare the result with a manual sum or filtered selection to confirm the compact formula returns the expected total.
Frequently Asked Questions
How do I sum Product A prices that are 20 or 50 in WPS Spreadsheets?
Click an empty cell and enter =SUM(SUMIFS(B2:B12,A2:A12,"Product A",B2:B12,{20,50})), then press Enter. Adjust B2:B12 and A2:A12 to match your data.
Can I use one SUMIFS formula to total multiple products at once?
Yes. Use an array constant for the product criteria: =SUM(SUMIFS(B2:B12,A2:A12,{"Product A","Product B"},B2:B12,{20,50})).
Do I need to press Ctrl+Shift+Enter for this SUM(SUMIFS(...)) formula?
In WPS Spreadsheets and modern Excel versions, normal Enter is usually enough because SUM aggregates the array result. If your version does not calculate correctly, press Ctrl+Shift+Enter to enter it as an array formula.
Why does my SUM(SUMIFS(...)) formula return 0 or an error?
Check that product names match exactly, prices are numeric rather than text, and your regional settings use commas or semicolons correctly for list separators.
