How to Use COUNTIFS with OR Conditions in Excel
Question details
The user needs to count specific rows in an Excel dataset that meet multiple criteria using OR logic, such as filtering by varying Priority and Status values while excluding Complete records.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating row counts where Priority is High/Medium/Low and Status is In Progress/Not Started, while ensuring records marked as Complete are excluded from the total.
- Observed behavior
- Requires a functional formula combination to accurately count rows that match multiple OR conditions across different columns without double-counting.
Ensure your data ranges are properly defined, of equal length, and do not contain merged cells, as these can cause errors in array formulas and COUNTIFS calculations.
Use SUM and COUNTIFS with Array Constants
This method uses an array constant inside COUNTIFS to handle OR conditions within the same criteria range, wrapped in a SUM function to calculate the total.
The COUNTIFS function natively uses AND logic for multiple criteria. By introducing an array constant (values wrapped in curly braces {}), you can force it to evaluate multiple OR criteria for a single range.
Click on the cell where you want the final count result to appear.
Type the formula: =SUM(COUNTIFS($E$7:$E$13,"High",$I$7:$I$13,{"In Progress","Not Started"})). Adjust the range references to match your actual data columns.
Press Enter. The formula will calculate the total number of rows where the priority is "High" AND the status is either "In Progress" OR "Not Started".
To count other priorities, simply change the "High" text in the formula to "Medium" or "Low" as needed.
Use the Not Equal To (<>) Operator for Exclusion
If you want to include all statuses except one specific status (e.g., "Complete"), using the exclusion operator is a much shorter and more efficient approach.
Use SUMPRODUCT for Complex OR Logic
SUMPRODUCT is a highly versatile alternative when dealing with complex arrays and multiple OR conditions across different columns that COUNTIFS cannot easily handle.
Easily Calculate Complex Formulas in WPS Office
WPS Spreadsheet fully supports advanced Excel functions like COUNTIFS, SUMPRODUCT, and array formulas. You can seamlessly open, edit, and analyze your complex workbooks for free with zero learning curve.
- 1. Download and Install: Download WPS Office for free from the official website and install it on your device.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel workbook.
- 3. Enter the Formula: Select a cell and type your =SUM(COUNTIFS(...)) or SUMPRODUCT formula exactly as you would in Excel.
- 4. View the Results: Press Enter to instantly process the data and view your calculated results.
Frequently Asked Questions
Can I use multiple OR conditions in different columns with COUNTIFS?
COUNTIFS natively uses AND logic between different criteria ranges. To use OR logic across entirely different columns (e.g., Column A is X OR Column B is Y), you typically need to add multiple COUNTIFS formulas together or use the SUMPRODUCT function.
Why does my COUNTIFS formula with an array constant return a lower number than expected?
If you use an array constant like {"In Progress","Not Started"} without wrapping the entire COUNTIFS function inside a SUM() function, the spreadsheet software will only return the count for the very first item in the array. Always use =SUM(COUNTIFS(...)) for this method.
How do I reference cells instead of typing text in the array constant?
Array constants {} do not accept cell references. If you want to use cell references for your OR criteria, you cannot use the curly braces. Instead, use SUMPRODUCT or wrap your COUNTIFS in a SUM function while referencing a vertical or horizontal range of cells, then enter it as an array formula.
Does WPS Spreadsheet support array formulas and COUNTIFS?
Yes, WPS Spreadsheet fully supports COUNTIFS, SUMPRODUCT, and advanced array formulas, ensuring complete compatibility with your existing Excel data and calculations.
