WPS Spreadsheets COUNTIFS Formula for AND and OR Criteria Across Excel Ranges
Question details
The user needs a WPS Spreadsheets formula that counts cells containing 'Below' in columns E:G only for rows where column D equals 3 or 4, and the formula must remain correct after sorting.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Counting text matches across a multi-column range with row-dependent OR criteria in a sorted data set.
- Observed behavior
- COUNTIFS does not directly support this AND/OR combination across differently shaped ranges, so the result can be incorrect after sorting when relying on fixed row positions.
Confirm the data range and whether 'Below' should be treated as a partial or exact text match. In WPS Spreadsheets, click the target sheet and note the column letters D, E, G before entering the formula.
Use SUMPRODUCT with SEARCH to count cells containing 'Below' where class is 3 or 4
Use this solution when cells in E:G may contain extra text around 'Below' and the corresponding class in column D must be 3 or 4.
SUMPRODUCT evaluates each row condition separately and then sums the resulting TRUE/FALSE values. The SEARCH function makes the formula tolerate text such as 'Below grade' instead of requiring an exact match.
Open the workbook in WPS Office, click the SLD sheet tab, and select the cell where you want the total count to appear.
Click the formula bar, paste =SUMPRODUCT(--ISNUMBER(SEARCH("Below",SLD!E7:G128)),--((SLD!D7:D128=3)+(SLD!D7:D128=4)>0)), and press Enter.
Use SUMPRODUCT with direct comparison for exact 'Below' matches
Use this shorter formula when cells in E:G contain exactly the text 'Below' and no extra text.
Count Cells with Multi-Criteria Formulas in WPS Spreadsheets
WPS Spreadsheets supports Excel-compatible SUMPRODUCT formulas, so you can count cells in E:G where column D is 3 or 4 and keep correct results after sorting.
- 1. Open WPS Spreadsheets: Launch WPS Office and open your .xlsx workbook, then click the worksheet that contains your data.
- 2. Select the result cell: Click the blank cell where the count should appear.
- 3. Apply the SUMPRODUCT formula: Type or paste the formula into the formula bar and press Enter.
- 4. Verify after sorting: Sort or filter the data and confirm the count still updates according to column D values instead of fixed row positions.
Frequently Asked Questions
Why doesn't COUNTIFS work for this AND and OR criteria?
COUNTIFS applies all conditions as AND logic and requires compatible range shapes. Because the OR condition for class 3 or 4 is in a single column D while the text condition is in E:G, SUMPRODUCT is more reliable.
Can I count only cells that exactly equal 'Below'?
Yes. Use =SUMPRODUCT((SLD!E7:G128="Below")*(((SLD!D7:D128=3)+(SLD!D7:D128=4)>0)) in the WPS Spreadsheets formula bar.
Will the formula still work after sorting the data?
Yes. The SUMPRODUCT formula uses row-aligned criteria against column D, so it does not rely on fixed row positions.
How do I change the class numbers from 3 or 4 to other values?
Replace 3 and 4 in the formula with your new class numbers, or add another term with a plus sign for additional OR conditions.
Does WPS Spreadsheets support SUMPRODUCT formulas?
Yes. WPS Spreadsheets supports SUMPRODUCT and other Excel-compatible formulas in .xlsx workbooks.
