How to Use COUNTIFS in Excel (and Google Sheets) with Multiple Criteria
1. Understanding COUNTIFS Basics
COUNTIFS counts rows that satisfy all of the supplied conditions. It is the multi‑criteria counterpart to COUNTIF.
Syntax
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)
| Argument | Description |
|---|---|
| criteria_range1 | First range to evaluate. |
| criteria1 | Condition applied to criteria_range1 (e.g., ">=10" or "Apple"). |
| criteria_range2 | Optional second range; must be the same size as the first. |
| criteria2 | Condition for the second range. |
| … | Up to 127 range/criteria pairs are allowed. |
All ranges must be identical in shape (same number of rows & columns). The result is a single number: the count of rows where every condition is true.
Simple Example
| A (Product) | B (Region) | C (Units) |
|---|---|---|
| Apple | East | 12 |
| Banana | West | 8 |
| Apple | West | 15 |
| Apple | East | 7 |
Goal: Count Apple sales in the East region.
=COUNTIFS(A2:A5, "Apple", B2:B5, "East")
Result: 2 (rows 1 and 4).
Key Points
- Wildcards –
"Apple*"matches any text that starts with Apple. Use*(any number of characters) or?(single character). - Logical operators – Enclose them in quotes, e.g.
">=10"or"<>"&"Cancelled". - Dates – Either type a date literal (
"2024-01-01") or concatenate a cell reference (">="&D1). - Case‑insensitivity –
COUNTIFSignores case;"apple"matches"Apple".
Common Pitfalls
| Pitfall | Why it happens | Quick fix |
|---|---|---|
| Mismatched range sizes | Returns #VALUE! if any range differs in rows/columns. | Ensure every range covers the same rows (e.g., A2:A100 with B2:B100). |
| Mixing data types | Numbers stored as text won’t match numeric criteria. | Convert text numbers to real numbers (VALUE) or use text criteria ("10"). |
Concatenating conditions with & in a single range |
COUNTIFS does not accept " >5"&"<10" . |
Use separate COUNTIFS arguments or switch to COUNTIFS + SUM. |
2. Setting Up Multiple Criteria
Each criterion must be paired with its own range; the function counts only rows where all pairs are true (logical AND).
=COUNTIFS(range1, criteria1, [range2, criteria2], …)
Example Data
| A (Region) | B (Product) | C (Units Sold) |
|---|---|---|
| East | Widget | 12 |
| West | Gadget | 8 |
| East | Gadget | 15 |
| North | Widget | 9 |
| East | Widget | 7 |
Goal: Count East‑region sales of Widget with at least 10 units.
=COUNTIFS(A2:A6, "East", B2:B6, "Widget", C2:C6, ">=10")
Result: 1 (only the first row meets all three criteria).
Reliable Setup Tips
- Same‑size ranges – All range arguments must have identical dimensions.
- Consistent data types – Store dates as true dates, numbers as numbers.
- Dynamic criteria – Reference cells for criteria so the count updates automatically:
excel
=COUNTIFS(A2:A100, $E$1, B2:B100, $F$1)
- Wildcards –
*matches any number of characters,?matches one.
excel
=COUNTIFS(B2:B6, "Wid*") 'matches “Widget”, “Widgadget”, etc.
- Avoid whole‑column references in pre‑365 Excel (e.g.,
A:A) to keep calculations fast; use explicit ranges likeA2:A1000.
3. Using Logical Operators with COUNTIFS
Logical operators are placed inside the criteria string and must be quoted.
| Operator | Meaning | Example |
|---|---|---|
> |
Greater than | ">5" |
< |
Less than | "<100" |
>= |
Greater than or equal | ">=2023-01-01" |
<= |
Less than or equal | "< =30" |
<> |
Not equal | "<>"&"Cancelled" |
= |
Equal (optional) | "Red" or "=Red" |
Numeric Example
| A (Region) | B (Units) | C (Revenue) |
|---|---|---|
| East | 12 | 4500 |
| West | 8 | 3200 |
| East | 20 | 7800 |
| South | 5 | 2100 |
| West | 15 | 6100 |
Goal: Count rows where Units ≥ 10 and Revenue < 7 000.
=COUNTIFS(B2:B6, ">=10", C2:C6, "<7000")
Result: 3 (rows 1, 3, 5).
Not‑Equal Example
=COUNTIFS(A2:A6, "<>East", C2:C6, ">3000")
Result: 2 (the two West rows).
Wildcards + Operators (text)
=COUNTIFS(A2:A10, "A*", A2:A10, "<>Apple")
Counts cells that start with “A” but are not exactly “Apple”.
Tip: When a criterion contains a literal *, ?, or ~, prefix it with ~ to treat it as a regular character.
4. Common Errors and How to Avoid Them
| Error | Why it Happens | Fix |
|---|---|---|
| #VALUE! – mismatched ranges | One range is a whole column, another is a limited range. | Make all ranges the same size, e.g., A2:A100 with B2:B100. |
| #NAME? – missing quotes | Operator not quoted (>100 instead of ">100"). |
Always wrap operators in double quotes. |
| Zero or unexpected count | Criteria data type mismatch (numbers stored as text). | Convert to proper type or use text criteria ("10"). |
#N/A – using & to join conditions |
COUNTIFS cannot evaluate concatenated conditions. |
Separate each condition into its own argument or use SUM(COUNTIFS(...),COUNTIFS(...)). |
| Wildcards on numbers | "*5" treats numbers as text, returning 0. |
Use numeric comparisons (">=5" and "<6"). |
| Escaping special characters | * or ? inside the target string is read as a wildcard. |
Prefix with ~ (e.g., "~*"). |
5. Advanced Tips & Variations
| Goal | Formula | Explanation |
|---|---|---|
| OR logic (e.g., region = East or West) | =SUM(COUNTIFS(A2:A100,"East"), COUNTIFS(A2:A100,"West")) |
COUNTIFS handles only AND; wrap separate calls in SUM. |
| Case‑sensitive count | =SUMPRODUCT(--EXACT(A2:A100,"Apple"), --(B2:B100>=DATE(2023,1,1))) |
EXACT forces case match; SUMPRODUCT aggregates. |
| Count blanks | =COUNTIFS(A2:A100,"") |
Empty string matches blank cells. |
| Count non‑blanks | =COUNTIFS(A2:A100,"<>") |
<> matches any non‑blank value. |
| Dynamic list of criteria (e.g., regions in D2:D5) | =SUMPRODUCT(COUNTIFS(A2:A100,D2:D5,B2:B100,">=2023-01-01")) |
COUNTIFS returns an array when the first criteria is a vertical range; SUMPRODUCT adds them. |
| Readable long formulas (Excel 365/2022) | =LET(rngA,A2:A100, rngB,B2:B100, critA,"Online", critB,">="&DATE(2023,1,1), COUNTIFS(rngA,critA,rngB,critB)) |
LET names ranges/criteria, making maintenance easier. |
| Avoid double‑counting overlapping criteria | =SUM(COUNTIFS(A2:A100,"Online",B2:B100,">=2023-01-01"), COUNTIFS(A2:A100,"Retail",B2:B100,">=2023-01-01")) |
Separate counts before summing. |
Note: Functions such as
LET,LAMBDA,FILTER,UNIQUE, andXLOOKUPrequire Excel 365/2021 or later. Google Sheets equivalents (ARRAYFORMULA,QUERY,IMPORTRANGE,REGEX*) are not available in Excel.
6. FAQ
What is the syntax of the COUNTIFS function in Excel?
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …) – each criteria_range must be the same size, and each criteria is a string (e.g., ">=10"), a number, or a cell reference. The function returns the count of rows where all criteria are met.
How do I use COUNTIFS to filter by dates?
Enter dates as literals ("2024-01-01") or concatenate a cell reference with an operator: =COUNTIFS(A2:A100, ">="&D1, A2:A100, "<="&E1). Ensure the date column contains true Excel dates, not text.
Can I combine COUNTIFS with other functions for dynamic ranges?
Yes. Wrap COUNTIFS inside SUMPRODUCT, LET, or IF to make the ranges or criteria dynamic. Example: =SUMPRODUCT(COUNTIFS(A2:A100, D2:D5, B2:B100, ">0")) counts rows that meet any region listed in D2:D5.
What are the limitations of COUNTIFS in older Excel versions?
Pre‑365/2021 Excel supports up to 127 range/criteria pairs but does not allow whole‑column references (A:A) in COUNTIFS. Also, functions like LET, LAMBDA, and dynamic arrays are unavailable, so you must use explicit ranges and helper columns for more complex logic.
How do I troubleshoot a COUNTIFS formula that returns zero?
- Verify that each criteria_range is the same size.
- Check data types – numbers stored as text won’t match numeric criteria.
- Confirm that the criteria strings are correctly quoted (e.g.,
">=10"). - Test each condition separately with a simple
COUNTIFto isolate the failing part.
With these fundamentals, tips, and troubleshooting steps, you can wield COUNTIFS confidently across Excel and Google Sheets to extract precise counts from multi‑dimensional data.