Need to count rows that match two conditions at once? Or three? The COUNTIFS function in Excel lets you count cells that meet multiple conditions simultaneously — across text, numbers, dates, wildcards, and even blank cells. In this guide you will learn exactly how COUNTIFS works and walk through 8 real-world examples covering every scenario you are likely to encounter, from simple two-condition counts to OR logic, date ranges, and wildcard matching.
COUNTIF vs COUNTIFS — What Is the Difference?
Before diving into examples, it helps to understand why COUNTIFS exists alongside COUNTIF:
- One condition only
- One criteria range
- Available since Excel 2003
- Use for simple single-condition counts
- Up to 127 conditions
- Multiple criteria ranges
- Available since Excel 2007
- AND logic by default — all conditions must be true
COUNTIFS Syntax
| Argument | Required? | What it means |
|---|---|---|
| criteria_range1 | Required | The first column or range to evaluate against the first condition |
| criteria1 | Required | The first condition — text, number, expression, or cell reference |
| criteria_range2, criteria2 | Optional | Additional range + condition pairs. Each range must be the same size as criteria_range1. Up to 127 pairs allowed. |
#VALUE! error.
Example 1: Two Text Conditions (Region + Status)
The most common use of COUNTIFS is filtering by two text columns simultaneously — for example, counting how many orders in the North region have a status of "Completed".
Example 2: Count Between a Date Range
COUNTIFS handles date ranges by applying two conditions to the same date column — one for the start date and one for the end date. This is ideal for counting orders, tasks, or events within a specific month or quarter.
">="&E1. Never put the cell reference inside the quotes — it will be treated as literal text and return zero.
Example 3: Count Numbers Between Two Values
Apply two comparison conditions to the same column to count values within a numeric range — for example, scores between 60 and 80, or salaries between two thresholds.
">=" and "<=" to include the boundary values. Use ">" and "<" to exclude them. In the example above, Carol (60) and Eve (80) are included because of the equals signs.
Example 4: Wildcard Matching with *
COUNTIFS supports the * wildcard (any sequence of characters) and ? (exactly one character). This is useful when you need to count partial text matches — product families, department prefixes, or customer name patterns.
"Laptop*" starts with Laptop |
"*Pro*" contains Pro |
"???-2026" three chars then -2026 |
"~*" literal asterisk
Example 5: Three Conditions Combined
COUNTIFS scales effortlessly to three or more conditions — common in HR dashboards (department + grade + status), sales reporting (region + product + rep), and project tracking (priority + owner + phase).
Example 6: OR Logic — Count Either Condition
COUNTIFS applies AND logic by default, so getting OR logic requires one of two techniques: adding two COUNTIFS results together, or using an array constant inside SUM.
SUMPRODUCT with ISNUMBER(MATCH(...)) for deduplication in those edge cases.
Example 7: Count Blank and Non-Blank Cells
COUNTIFS can detect blank and non-blank cells using special criteria strings. This is useful for auditing data completeness — for example, finding tasks that have no assigned owner, or employees with a missing department.
Example 8: Dynamic Criteria from Dropdown Cells
Hard-coded criteria work for one-off counts, but dashboards need to update dynamically. Reference cells (ideally connected to Data Validation dropdowns) so that changing the filter value updates every COUNTIFS formula instantly.
Quick-Reference: All Comparison Operators in COUNTIFS
| Goal | Criteria syntax | Example |
|---|---|---|
| Equal to text | "North" | =COUNTIFS(A:A, "North") |
| Equal to cell value | "="&A1 | =COUNTIFS(A:A, "="&E1) |
| Not equal to | "<>Cancelled" | =COUNTIFS(B:B, "<>Cancelled") |
| Greater than | ">100" | =COUNTIFS(C:C, ">100") |
| Greater than or equal | ">="&E1 | =COUNTIFS(C:C, ">="&E1) |
| Less than or equal | "<="&E2 | =COUNTIFS(C:C, "<="&E2) |
| Blank / empty | "" | =COUNTIFS(D:D, "") |
| Not blank | "<>" | =COUNTIFS(D:D, "<>") |
| Starts with (wildcard) | "North*" | =COUNTIFS(A:A, "North*") |
| Contains (wildcard) | "*Pro*" | =COUNTIFS(A:A, "*Pro*") |
Troubleshooting: Why Is COUNTIFS Returning Zero?
If COUNTIFS returns 0 when you expect a positive number, work through these five common causes in order:
1. Criteria ranges are different sizes
Every criteria range must be exactly the same number of rows and columns as the first range. If criteria_range1 is A2:A100, then criteria_range2 must also be exactly 99 rows. A mismatched size returns #VALUE!.
2. Numbers stored as text
A numeric criteria like ">50" will not match cells that contain numbers formatted as text (look for the green triangle in the top-left corner of the cell). Fix: select the column → Data → Text to Columns → Finish.
3. Extra spaces in cell values
A cell containing "North " (trailing space) will not match the criteria "North". Clean your data with TRIM() or use a wildcard as a workaround: "North*".
4. Dates stored as text
Date comparison criteria only work when the dates in your column are genuine Excel serial dates. Press Ctrl+` to toggle formula view — real dates show as 5-digit numbers. Text-formatted dates will not match ">="&DATE(...).
5. Operator outside the quotes
This is wrong: =COUNTIFS(A:A, >"North"). Operators must be inside quotes, concatenated with & when combining with a cell reference: =COUNTIFS(B:B, ">"&E1).
=SUMPRODUCT((EXACT(A2:A100,"North"))*(B2:B100="Completed")) instead.
Frequently Asked Questions
-
What is the difference between COUNTIF and COUNTIFS in Excel?COUNTIF counts cells that meet a single condition in one range. COUNTIFS counts cells that meet multiple conditions across one or more ranges, using AND logic by default — every condition must be true for a row to be counted. COUNTIFS is available from Excel 2007 onwards.
-
Can COUNTIFS use OR logic?Yes. Add two COUNTIFS results together to apply OR logic:
=COUNTIFS(…) + COUNTIFS(…). For multiple OR values in the same column, use=SUM(COUNTIFS(range, {"A","B","C"}))with an array constant. Both methods count rows matching either condition. -
Can COUNTIFS count between two dates?Yes. Apply two criteria pairs to the same date column:
=COUNTIFS(A:A, ">="&E1, A:A, "<="&E2). Ensure dates are stored as real Excel serial dates and not text strings — press Ctrl+` to verify. -
Why is COUNTIFS returning zero when I know there are matches?The most common causes are: criteria ranges with different sizes, numbers stored as text, extra trailing spaces in cell values, dates formatted as text, or a comparison operator placed outside the quote marks. Work through each in order — see the troubleshooting section above.
-
How do I count blank or non-blank cells with COUNTIFS?Use
""to count blank cells and"<>"to count non-blank cells. Combine with other criteria:=COUNTIFS(B:B, "In Progress", C:C, "")counts "In Progress" rows with a blank owner column. -
Does COUNTIFS work with wildcards?Yes. Use
*for any sequence of characters and?for a single character. For example,"Laptop*"matches Laptop, Laptop Pro, Laptop 15", etc. To match a literal asterisk, prefix it with a tilde:"~*".