How to Use SUMIFS or SUMPRODUCT to Count Text Matches and Date Ranges in WPS Office Spreadsheets
Question details
The user needs to count rows in WPS Office Spreadsheets by a status such as Late, a subdivision that may appear inside longer text values, and a date range relative to a reference date.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is summarizing late subjects by subdivision and age range, where the subdivision text is embedded in a longer cell value.
- Observed behavior
- Counting must support partial text matches without wildcard characters and date filtering with EDATE-based age ranges.
Confirm that the status, date, and subdivision columns are in a consistent layout, and that date cells are real dates rather than text values. Identify the reference date cell such as H1 and the subdivision label cell such as H25 used in the formula.
Use SUMPRODUCT with SEARCH for partial text and date range counts
Use this single-cell formula when the subdivision name is embedded in longer text values and you do not want to add helper columns.
SUMPRODUCT evaluates each condition as an array of TRUE/FALSE values and multiplies them, so only rows that meet all conditions are counted. SEARCH finds the subdivision text anywhere in a cell, which removes the need for wildcard characters.
Open the workbook in WPS Office Spreadsheets and click the cell where you want the count to appear, for example I25.
Type =SUMPRODUCT(($B$2:$B$23="Late")*($D$2:$D$23<=EDATE($H$1,-3))*($D$2:$D$23>EDATE($H$1,-6))*ISNUMBER(SEARCH(H$25,$C$2:$C$23))) and press Enter.
Adjust the row ranges, status value, date column, and search cell to match your actual data layout if the result is incorrect.
Use SUMIFS with helper columns for cleanly separated data
Use SUMIFS when you can split or already have the subdivision and age group in separate columns, because it is easier to audit and scales well over large ranges.
Count Partial Text Matches and Date Ranges with WPS Spreadsheets
WPS Office Spreadsheets supports the same SUMPRODUCT, SEARCH, EDATE, and SUMIFS functions, so you can count late subjects by subdivision and age range directly in your workbook without switching tools.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the Excel or WPS workbook containing the late subject data.
- 2. Select the output cell: Click the cell where the count should appear, such as I25.
- 3. Enter the recommended formula: Type the SUMPRODUCT/SEARCH formula or the SUMIFS helper-column formula and press Enter.
- 4. Save the workbook: Press Ctrl+S or go to Menu > Save to keep the formula results and formatting.
Frequently Asked Questions
Why does SEARCH work for partial text matches without wildcards?
SEARCH returns the starting position of a text string within another text string. Combining it with ISNUMBER inside SUMPRODUCT converts that position into a TRUE/FALSE condition, so a wildcard character is not needed.
Can SUMIFS count partial text matches inside longer cells?
SUMIFS can use wildcards such as "*subdivision*", but when many subdivision names overlap, wildcards may match the wrong value. The more robust partial-match approach is SUMPRODUCT with SEARCH, or you can normalize the data into a separate subdivision column and use SUMIFS.
What does EDATE(H1,-3) do in this count?
EDATE returns a date that is a specified number of months before or after the start date. EDATE(H1,-3) returns the date three months before H1, and EDATE(H1,-6) returns six months before H1, which creates the 3-6 month age range.
How can I prevent a shorter subdivision name from matching a longer subdivision name?
Use exact boundary checks such as searching for the subdivision with delimiters, or normalize the subdivision into its own helper column. SUMPRODUCT with SEARCH alone may match a shorter name inside a longer one.
Is the SUMPRODUCT formula case-sensitive?
No. SEARCH is case-insensitive, so it matches uppercase and lowercase subdivision text equally. If you need case-sensitive matching, use FIND instead of SEARCH.
