Use SUMPRODUCT and SEARCH Criteria to Count Matching Rows in WPS Office Excel
Question details
User needs to count rows in a range that match a buyer header and contain a specified text string using SUMPRODUCT with SEARCH and ISNUMBER in WPS Office Excel.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Counting rows in a table where a buyer column matches a header value and a text field contains a target string.
- Observed behavior
- The worksheet should return a numeric count based on both the buyer header and text criteria, but the user needs the exact formula and how to apply it.
Confirm the buyer header values are in row 1 and the data ranges, such as $B$2:$B$9 and $A$2:$A$9, match the actual worksheet layout. Ensure the result cell is empty before entering the formula.
Use SUMPRODUCT with ISNUMBER and SEARCH to Count Matching Rows
Use this formula when you need a single cell to count rows that match a buyer header and contain a specified text string, and you want to copy the formula across and down.
The formula multiplies two logical arrays: one checks whether the buyer column equals the header value, and the other checks whether SEARCH finds the target text in column A. ISNUMBER converts SEARCH positions to TRUE/FALSE so SUMPRODUCT can count matching rows.
Click the cell where you want the count to appear, such as F2 in the result area.
Type =SUMPRODUCT(($B$2:$B$9=F$1)*(ISNUMBER(SEARCH($D2,$A$2:$A$9)))) and press Enter.
Drag the fill handle or copy the formula to adjacent result cells as needed to apply the same criteria to other headers and search texts.
Count Matching Text Rows with SUMPRODUCT in WPS Office Excel
WPS Office Excel supports the same SUMPRODUCT, ISNUMBER, and SEARCH functions, so you can apply this formula directly in WPS Spreadsheets to count rows that match a buyer header and contain a specified text string.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the buyer and text data.
- 2. Select the result cell: Click the cell where the count should appear, such as F2.
- 3. Enter the SUMPRODUCT formula: Type =SUMPRODUCT(($B$2:$B$9=F$1)*(ISNUMBER(SEARCH($D2,$A$2:$A$9)))) and press Enter.
- 4. Copy the formula: Drag the fill handle across and down to apply the formula to other result cells.
- 5. Verify the count: Check that the returned count matches the buyer header and the specified text criteria.
Frequently Asked Questions
Can I use COUNTIF instead of SUMPRODUCT for this?
COUNTIF alone cannot handle the combination of a header match and a partial text SEARCH across different columns. The SUMPRODUCT formula with ISNUMBER(SEARCH(...)) is the reliable way to count rows meeting both conditions.
What does the ISNUMBER(SEARCH(...)) part do?
SEARCH returns the position of the text in D2 within column A, or an error if not found. ISNUMBER converts that result to TRUE when the text is present, so SUMPRODUCT can count it.
How do I adjust the formula if my data has more rows?
Replace the ranges $B$2:$B$9 and $A$2:$A$9 with the actual row ranges, keeping the dollar signs to lock columns or rows where needed.
Can I copy the formula across and down?
Yes. The mixed references F$1 and $D2 are designed so the header reference moves across columns and the search text moves down rows while the data ranges stay fixed.
