How to Sum Excel Values Matching Multiple Criteria in WPS Office
Question details
The user needs to add all values in a worksheet that match two conditions, such as an item code and a country, rather than returning only the first matching value.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has columns such as item code, country, and numeric values, and wants a formula that totals every row matching a specific item code and country.
- Observed behavior
- A basic lookup returns only the first matching value instead of summing every matching value for the specified criteria.
Before writing the formula, confirm which columns contain the sum values and the two criteria columns. Also make sure the criteria cells you plan to reference, such as A2 and B2, contain the item code and country exactly as they appear in the data.
Use SUMIFS to Sum All Rows Matching Two Criteria
Use this built-in WPS Spreadsheets formula when you want a fast, straightforward way to total every value that matches the specified item code and country.
SUMIFS is designed for multiple criteria. The first argument is the column to sum, and each additional pair defines a criteria range and its condition.
Click the cell where you want the total to appear, such as D2.
Type =SUMIFS(L:L,J:J,A2,K:K,B2) into the cell or the formula bar.
Press Enter. If your data uses different columns, adjust L:L, J:J, and K:K to match your worksheet layout.
Use SUMPRODUCT to Sum with Multiplied Criteria Arrays
Use this alternative when you prefer fixed ranges or need a formula that evaluates each criterion as an array and sums the resulting product.
Total Matching Values in WPS Office Spreadsheets with SUMIFS
Open your workbook in WPS Office Spreadsheets and use the built-in SUMIFS function to add every value that matches the item code and country criteria. No extra plugins are required.
- 1. Open your workbook in WPS Office Spreadsheets: Start WPS Office, click Menu, and open the Excel-compatible file that contains your data.
- 2. Select the result cell: Click the empty cell where you want the summed total to be displayed.
- 3. Type the SUMIFS formula: In the formula bar, type =SUMIFS(L:L,J:J,A2,K:K,B2), replacing column letters and criteria cell references as needed.
- 4. Press Enter: Press Enter to calculate the total of all rows that match both criteria.
- 5. Check the result: Confirm that the returned total includes every matching item code and country in your data range.
Frequently Asked Questions
What is the difference between SUMIFS and SUMPRODUCT for summing with multiple criteria?
SUMIFS uses explicit criteria ranges and is often easier to read, while SUMPRODUCT multiplies condition arrays and requires fixed ranges. Both return the same total when the ranges and criteria are correct.
Can I use SUMIFS with full column references like L:L?
Yes, WPS Office Spreadsheets accepts full column references such as L:L. For very large sheets, using a limited range like L2:L1000 can improve performance.
How do I add a third condition to a SUMIFS formula?
Add another criteria range and criteria pair after the existing ones, for example =SUMIFS(L:L,J:J,A2,K:K,B2,M:M,C2). Each new condition must be paired as range, criteria.
Why does my SUMIFS formula return 0?
The criteria values may not match exactly. Check for extra spaces, text-formatted numbers, or mismatched cell references, and confirm that the criteria cells contain values exactly as they appear in the data.
Can I apply this to Excel files created in Microsoft Excel?
Yes. WPS Office Spreadsheets opens and saves .xlsx and .xls files, and SUMIFS and SUMPRODUCT formulas work the same way as in Microsoft Excel.
