How to Sum Multiple Columns Based on One Criterion in WPS Office Spreadsheets
Question details
The user needs an Excel-compatible formula to total values across multiple columns, specifically F through J, only for rows where column A matches a specified criterion.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a data range where column A holds criteria and columns F:J hold numeric values. They want a single formula result or per-column totals without creating helper columns.
- Observed behavior
- The user is looking for a formula-based method rather than manually filtering or using multiple SUMIF formulas.
Make sure the criterion column and value columns share the same row range, for example A2:A16 and F2:J16, and place the criterion in a separate cell such as A19. Check whether your spreadsheet version supports dynamic array functions if you plan to use the FILTER formula.
Use the MMULT/TRANSPOSE Formula for Fixed-Range Totals
Best for classic Excel and WPS Office versions because it handles the entire F2:J16 range in one matrix calculation and returns the total for all matching rows.
The MMULT function multiplies the transposed value range by a column of TRUE/FALSE values from the criterion range, producing a vertical array of column totals. The outer SUM returns a single grand total.
Type the criterion value into an empty cell, such as A19, so the formula can reference it instead of hard-coding the value.
Click the cell where you want the sum to appear.
Type =SUM(MMULT(TRANSPOSE(F2:J16),--(A2:A16=A19))). In classic Excel versions, press Ctrl+Shift+Enter to confirm it as an array formula.
Confirm that the result equals the sum of F2:J16 for rows where column A matches A19.
Use SUMPRODUCT as a Simpler Non-Array Formula
Use this alternative when you want a simpler formula that normally does not require array entry and is easy to adjust.
Use FILTER and SUM for Dynamic-Array Versions
Use this method if you are on Microsoft 365 or a WPS Office version that supports dynamic array functions.
Sum Multiple Columns by One Criterion in WPS Office Spreadsheets
Use WPS Office Spreadsheets to build the same formula-based calculation as Excel. You can enter the MMULT/TRANSPOSE or SUMPRODUCT formula directly in a worksheet to total columns F:J where column A meets your criterion.
- 1. Open the workbook: Launch WPS Office Spreadsheets, click Menu > Open, and select the Excel workbook that contains columns A and F:J.
- 2. Add the criterion: Click an empty cell such as A19 and type the criterion value that column A must match.
- 3. Enter the formula: Click the result cell and enter =SUMPRODUCT((A2:A16=A19)*F2:J16) or the MMULT formula shown above.
- 4. Check and save the result: Press Enter, verify the total, then save the workbook as an .xlsx file to keep Excel compatibility.
Frequently Asked Questions
Can I sum multiple columns with one criterion without adding helper columns?
Yes. In WPS Office Spreadsheets you can enter =SUMPRODUCT((A2:A16=A19)*F2:J16) to get the grand total without helper columns, or use the MMULT/TRANSPOSE formula if you prefer a matrix calculation.
Does the formula work in WPS Office if my file was created in Microsoft Excel?
Yes. WPS Office Spreadsheets opens Excel .xlsx workbooks and supports the SUMPRODUCT and MMULT functions used in this solution. Check the formula ranges if the data layout changes.
How can I get separate totals for columns F, G, H, I, and J instead of a single sum?
Select five cells in a vertical range, enter =MMULT(TRANSPOSE(F2:J16),--(A2:A16=A19)), and confirm it as an array formula if required. Each cell will show the total for one of the five columns.
What should I do if the formula returns #VALUE! or #NAME? in WPS Office?
Check that all ranges have the same number of rows and that F2:J16 contains only numbers. If #NAME? appears, your version may not support the FILTER function; use the SUMPRODUCT or MMULT formula instead.
