Use SUMIFS to Include or Exclude Text in WPS Spreadsheets
Question details
Use the SUMIFS function to total amounts when a reference cell contains specific text or does not contain that text.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The worksheet has dates in column A, references in column B, amounts in column C, and requires separate totals for references that include or exclude a text string such as ppr.
- Observed behavior
- The desired result is a date-based total filtered by whether the reference text contains the specified string.
Confirm that the amount range, reference range, and date range start and end on the same rows. Replace "ppr" with the text you want to match, and keep the asterisks to match text appearing anywhere in a reference.
Sum values when the reference contains specific text
Use this formula when you need to add amounts for a date only when the related reference includes a text string.
The asterisk (*) is a wildcard that represents any number of characters. Therefore, "*ppr*" matches references containing ppr before, after, or within other text.
In WPS Spreadsheets, click the cell where you want the total for the date shown in A2.
Type =SUMIFS($C$2:$C$100,$B$2:$B$100,"*ppr*",$A$2:$A$100,A2) and press Enter. This sums C2:C100 when column B contains ppr and column A matches the date in A2.
Select the formula cell and drag the fill handle down, or use Home > Fill > Down, to calculate the matching total for each date.
Sum values when the reference does not contain specific text
Use this formula when you need a date-based total that excludes every reference containing the specified text.
Use SUMIFS Wildcards to Filter Text and Sum Amounts in WPS Spreadsheets
WPS Spreadsheets lets you create SUMIFS formulas that total values by date and text criteria. Use wildcard text such as *ppr* to include matching references or <>*ppr* to exclude them, then fill the formula down for the remaining dates.
- 1. Open the worksheet in WPS Spreadsheets: Open your .xlsx or .xls file, then verify that dates, references, and amounts are arranged in separate columns.
- 2. Choose the output cell: Click the cell beside the first date where the filtered total should appear.
- 3. Enter an include-text SUMIFS formula: Enter =SUMIFS($C$2:$C$100,$B$2:$B$100,"*ppr*",$A$2:$A$100,A2) to sum amounts for references containing ppr.
- 4. Enter an exclude-text formula if needed: In another result column, enter =SUMIFS($C$2:$C$100,$B$2:$B$100,"<>*ppr*",$A$2:$A$100,A2) to exclude references containing ppr.
- 5. Fill results for all dates: Drag the fill handle from each formula cell down the column to calculate the remaining date totals.
Frequently Asked Questions
What does *ppr* mean in a SUMIFS criterion?
The asterisks are wildcards. In WPS Spreadsheets, "*ppr*" matches any reference that contains ppr anywhere in its text, including values such as PPR-12 or project-ppr.
How do I exclude cells that contain a word in SUMIFS?
Place <> before the wildcard criterion. For example, use "<>*ppr*" as the criteria argument for the reference range to sum only rows whose references do not contain ppr.
Can I use SUMIFS to match both a date and text in another column?
Yes. Add each range and criterion pair to SUMIFS. For example, =SUMIFS($C$2:$C$100,$B$2:$B$100,"*ppr*",$A$2:$A$100,A2) matches both the text in column B and the date in column A.
Why is my SUMIFS formula not returning the expected total?
Check that the sum range and every criteria range have the same number of rows, that the date values are real dates rather than text, and that the text criterion includes the quotation marks and wildcard asterisks.
