Use SUMIFS in WPS Spreadsheets to Sum Values by Two Criteria
Question details
The user needs to add values from one column only when two other columns meet specified text criteria.
- Product
- Microsoft Excel or WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Records must be filtered by a word in one column and another word in a second column before values in a third column are totaled.
- Observed behavior
- The required result is a single sum based on two matching conditions, without using nested XLOOKUP formulas.
Identify the column containing the values to add and the two columns containing the criteria. Confirm that the criteria cells, such as D451 and B451, contain the exact text you want to match.
Use SUMIFS to sum values that match two criteria
Use this formula when one value column must be totaled only for rows matching criteria in two separate columns.
SUMIFS uses the syntax SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2). The sum range and each criteria range must cover the same rows.
Open the worksheet in WPS Spreadsheets and click the cell where you want the conditional total to appear.
Type =SUMIFS($Z:$Z,$E:$E,D451,$B:$B,B451), using column Z for values to add, column E for the first condition, and column B for the second condition.
Replace $Z:$Z, $E:$E, $B:$B, D451, and B451 with the actual sum range, criteria ranges, and criteria cells in your worksheet.
Press Enter to calculate the total. The result includes only rows where both criteria match their corresponding cells.
Enter explicit text criteria in SUMIFS
Use this variation when the two conditions are fixed words rather than values stored in worksheet cells.
Calculate Two-Criteria Totals with SUMIFS in WPS Spreadsheets
WPS Spreadsheets lets you enter SUMIFS formulas to total values that meet multiple conditions, with a familiar spreadsheet interface and strong Microsoft Excel file compatibility.
- 1. Open your spreadsheet: Launch WPS Office, choose Spreadsheets, and open the workbook containing your records.
- 2. Select the result cell: Click the cell where the two-criteria total should appear.
- 3. Enter the formula: Type =SUMIFS($Z:$Z,$E:$E,D451,$B:$B,B451), then replace the sample references with your actual ranges and criteria cells.
- 4. Confirm and review: Press Enter and check that the returned total includes only rows meeting both conditions.
Frequently Asked Questions
What does the SUMIFS formula do with two criteria?
SUMIFS adds numbers from a sum range only when every supplied condition is met. For example, =SUMIFS($Z:$Z,$E:$E,D451,$B:$B,B451) checks columns E and B before adding values from column Z.
Can I use words directly instead of cell references in SUMIFS?
Yes. Put text criteria in quotation marks, such as =SUMIFS($Z$2:$Z$1000,$E$2:$E$1000,"North",$B$2:$B$1000,"Completed").
Why does SUMIFS return zero?
Check that the criteria text matches the worksheet values, including spelling and extra spaces, and confirm that the sum range and criteria ranges have the same dimensions.
Should I use whole-column references in a large workbook?
Whole-column references such as $Z:$Z are convenient, but bounded ranges such as $Z$2:$Z$1000 can improve calculation performance in large WPS Spreadsheets or Excel workbooks.
