WPS Spreadsheet SUMPRODUCT: Sum Values Between Limits with Multiple Criteria
Question details
The user needs to sum spreadsheet values only when multiple text and numeric criteria are satisfied.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet must find text containing specific values in columns D and H, then include rows where the value in column AL is greater than 50 and less than 100.
- Observed behavior
- The calculation requires several conditions, including partial text matches and an exclusive numeric range, before returning a total.
Confirm that the criteria ranges and the value range contain the same number of rows and that the worksheet name is correct. Decide whether column AL or another column, such as P, contains the values you want to add.
Use SUMPRODUCT to sum values matching text and numeric limits
Use this solution when all criteria must be evaluated in one formula, including partial text matches in columns D and H.
SUMPRODUCT treats each condition as an array of TRUE or FALSE results and adds only the rows that satisfy every condition. SEARCH performs case-insensitive partial matching; replace it with FIND when the text match must be case-sensitive.
In WPS Spreadsheet, click the cell where you want the total to appear.
Enter =SUMPRODUCT((ONE!$AL$9:$AL$3344)*(ISNUMBER(SEARCH("1",ONE!$D$9:$D$3344)))*(ISNUMBER(SEARCH("2",ONE!$H$9:$H$3344)))*(ONE!$AL$9:$AL$3344>50)*(ONE!$AL$9:$AL$3344<100)) and press Enter.
The formula searches column D for text containing 1 and column H for text containing 2. To use different criteria, replace the quoted values "1" and "2".
If the amounts to add are in column P rather than column AL, replace the first ONE!$AL$9:$AL$3344 range with ONE!$P$9:$P$3344 while keeping the AL ranges for the numeric limits.
Replace SEARCH with FIND in both text conditions, such as ISNUMBER(FIND("1",ONE!$D$9:$D$3344)), when uppercase and lowercase text must be distinguished.
Build Multi-Criteria SUMPRODUCT Formulas in WPS Spreadsheet
WPS Spreadsheet lets you create and edit SUMPRODUCT formulas for combined text and numeric conditions, with support for familiar Excel-compatible functions and worksheets.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing the source data.
- 2. Select the output cell: Click the cell where the calculated total should be displayed.
- 3. Enter a SUMPRODUCT formula: Type the multi-criteria SUMPRODUCT formula into the formula bar, adjusting the sheet name, ranges, and search text as needed.
- 4. Confirm and review the result: Press Enter, then compare the result with the rows that contain the required text and values between the selected limits.
Frequently Asked Questions
How do I sum values only when columns D and H contain specific text?
In WPS Spreadsheet, use ISNUMBER(SEARCH("text",range)) for each text condition inside SUMPRODUCT. For example, use SEARCH("1",ONE!$D$9:$D$3344) and SEARCH("2",ONE!$H$9:$H$3344).
Does the formula include values equal to 50 or 100?
No. The conditions >50 and <100 create an exclusive range, so 50 and 100 are excluded. Use >=50 and <=100 to include those boundary values.
How can I make the text search case-sensitive?
Replace SEARCH with FIND in each text condition. FIND distinguishes uppercase and lowercase characters, while SEARCH does not.
Why must all SUMPRODUCT ranges have the same size?
SUMPRODUCT evaluates corresponding rows across its arrays. If the ranges start or end on different rows, the formula can return an incorrect result or an error, so keep every range aligned.
What if the values to add are in column P instead of column AL?
Use ONE!$P$9:$P$3344 as the first SUMPRODUCT range, while retaining ONE!$AL$9:$AL$3344 for the greater-than-50 and less-than-100 tests.
