How to Use COUNTIFS in WPS Office Excel to Match All Listed Criteria
Question details
The user wants to count records that match a group and include each of two specified conditions in Excel using COUNTIFS, rather than counting rows that meet only one condition.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- A user has a dataset with a group column and a criteria column, and needs a formula to count rows belonging to a specific group that also have either of two listed values.
- Observed behavior
- A standard COUNTIFS formula cannot return the combined total for two values in the same column because its conditions are evaluated as AND.
Open your WPS Office Excel workbook and verify the exact ranges for the group column and the criteria column. Check that criteria values such as "Cold" and "Room Temp" match the cell text exactly, including spaces and capitalization.
Use SUM with COUNTIFS and an Array of Criteria
Best when you need a formula that returns the combined total for a group plus either of the listed criteria.
COUNTIFS normally evaluates all conditions as AND, so it cannot match two different values in the same column directly. Wrapping COUNTIFS in SUM with an array constant calculates separate counts for each listed value and adds them together.
Click an empty cell where you want the total count to appear.
Type =SUM(COUNTIFS(C2:C26,"Group",B2:B26,{"Cold","Room Temp"})) in the formula bar or selected cell.
Press Enter, or press Ctrl+Shift+Enter if your WPS Office version requires legacy array formula entry.
Compare the result against separate COUNTIFS totals for "Cold" and "Room Temp" to confirm both values are included.
Create a Pivot Table to Summarize Matching Records
Use this visual method if you prefer an interactive summary and want to avoid formula syntax.
Count Multi-Criteria Matches in WPS Office Excel with SUM and COUNTIFS
WPS Office Excel supports the same COUNTIFS, SUM, and array constant technique to count rows that match a group and any of the listed criteria. Open your .xlsx workbook and enter the formula directly in WPS Office.
- 1. Open your workbook: Open the target workbook in WPS Office Excel.
- 2. Select a result cell: Click an empty cell where the result should be displayed.
- 3. Enter the formula: Type =SUM(COUNTIFS(C2:C26,"Group",B2:B26,{"Cold","Room Temp"})).
- 4. Confirm the calculation: Press Enter, or Ctrl+Shift+Enter for legacy array entry.
- 5. Review the total: Check the returned total to confirm it includes both listed criteria.
Frequently Asked Questions
Why does COUNTIFS with two criteria in the same column return 0?
COUNTIFS evaluates conditions as AND, so one cell cannot equal both "Cold" and "Room Temp". Use =SUM(COUNTIFS(C2:C26,"Group",B2:B26,{"Cold","Room Temp"})) to sum separate COUNTIFS results for each value.
Can I use this formula in WPS Office Excel and Microsoft Excel?
Yes. WPS Office Excel supports COUNTIFS, SUM, and array constants in .xlsx workbooks, so the same formula works in both applications.
How can I use cell references instead of typing the criteria in the formula?
You can place "Cold" and "Room Temp" in cells and use a formula such as =SUMPRODUCT(COUNTIFS(C2:C26,"Group",B2:B26,D2:D3)), replacing D2:D3 with your criteria cells.
When should I use a pivot table instead of a formula?
Use a pivot table when you want an interactive summary. Select the data, go to Insert > PivotTable, and drag the group and temperature fields to Filters, Rows, and Values.
