How to Sum Values for a Person Across Multiple Excel Columns in WPS Office
Question details
The user wants to total values for a person when names are in one column and the values to add are stored in two adjacent columns in WPS Office Spreadsheets.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is creating a summary total from a table where a name column identifies rows and two adjacent value columns store the amounts.
- Observed behavior
- The user needs a formula that matches a person's name and returns the combined total from both adjacent value columns.
Before entering the formula, confirm the exact range that contains the names and the two adjacent value columns. Select a blank result cell and, if needed, type the person's name in a separate lookup cell.
Use SUMPRODUCT to Match a Name and Sum Adjacent Value Columns
Use this approach when names are in one column and the numbers to total are in two adjacent columns in WPS Office Spreadsheets.
The SUMPRODUCT function multiplies the TRUE/FALSE name matches by the values in both adjacent columns and returns the combined total. The name condition range and the value range must have the same number of rows.
Open the workbook in WPS Office Spreadsheets and click the blank cell where you want the person's total to appear.
Type =SUMPRODUCT(($A$2:$A$10=A13)*$B$2:$C$10). Replace $A$2:$A$10 with your name column range, A13 with the lookup cell containing the person's name, and $B$2:$C$10 with your two adjacent value columns.
Press Enter. WPS Spreadsheets calculates the total for the matching person across both value columns.
Calculate Person Totals Across Multiple Columns in WPS Office Spreadsheets
Use WPS Office Spreadsheets to open your Excel workbook, enter the SUMPRODUCT formula, and get the person's total from adjacent value columns without buying Microsoft 365.
- 1. Install WPS Office Free: Download WPS Office Free from the official WPS website and install it on your Windows or Mac computer.
- 2. Open the Excel workbook: Launch WPS Office Spreadsheets, click Menu > Open, and select the Excel file that contains the names and value columns.
- 3. Enter the SUMPRODUCT formula: Click a blank result cell and type =SUMPRODUCT(($A$2:$A$10=A13)*$B$2:$C$10), adjusting the ranges to match your sheet.
- 4. Press Enter to calculate: Press Enter to display the total for the selected person's name across both adjacent value columns.
Frequently Asked Questions
Can this formula sum two value columns that are not next to each other?
No. A single SUMPRODUCT range such as $B$2:$C$10 adds adjacent columns only. For non-adjacent columns, use two SUMPRODUCT expressions added together: =SUMPRODUCT(($A$2:$A$10=A13)*$B$2:$B$10)+SUMPRODUCT(($A$2:$A$10=A13)*$D$2:$D$10).
How do I change the formula if my data starts in a different row?
Replace $A$2:$A$10 with your actual name range, $B$2:$C$10 with your actual adjacent value range, and A13 with the cell that contains the person's name. The name range and value range must have the same number of rows.
Why does my SUMPRODUCT result show #N/A?
Check that the lookup cell contains a name that exists in the name range and that all ranges are the same height. #N/A often appears when a match is not found or the ranges have mismatched sizes.
Can I use SUMIFS instead of SUMPRODUCT for this task?
SUMIFS can sum one value column directly, but SUMPRODUCT is useful here because it can total a two-column value range in one formula.
Does WPS Office support SUMPRODUCT when opening Excel files?
Yes. WPS Office Spreadsheets supports SUMPRODUCT and opens Excel workbooks in .xlsx, .xls, and .csv formats.
