COUNTIFS With Multiple Conditions in Excel
Count rows that match two or more criteria simultaneously. Essential for dashboards, KPI tracking, and data validation.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Count orders where status is Completed and amount is greater than 500"
=COUNTIFS(A2:A100,"Completed",B2:B100,">500")COUNTIFS counts rows where ALL criteria match. Here it counts rows where column A is Completed AND column B exceeds 500.
Step-by-step breakdown
- A2:A100,"Completed" — first criteria: status must be Completed
- B2:B100,">500" — second criteria: amount must exceed 500
- Both conditions must be TRUE in the same row to be counted
- You can add more criteria pairs for additional conditions
Edge cases & warnings
- Returns 0 when no rows match (no error to handle)
- Text matching is case-insensitive
- Wildcards: use * for any characters, ? for single character
- Number criteria must be in quotes when using operators: ">500" not >500
Examples
"Status="Completed", Amount>500"
Count of high-value completed orders"Status="Pending", Amount>1000"
Count of large pending ordersFrequently asked questions
Can COUNTIFS use OR logic?
Not directly. Add multiple COUNTIFS: =COUNTIFS(A:A,"Done")+COUNTIFS(A:A,"Shipped") counts rows that are Done OR Shipped.
How many criteria can COUNTIFS handle?
Up to 127 criteria pairs. In practice, if you need more than 5-6, consider restructuring your data or using a PivotTable.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.