WHAT DOES COUNTIFS DO
The COUNTIFS function counts how many rows match all the criteria you provide. It’s like COUNTIF, but supports multiple conditions.
For example, you can count orders where the region is West and the product is B.
WHEN TO USE IT
- You need to count values using multiple filters
- Your data is organized in a table-like structure
- You want to avoid helper columns
Tip: All the criteria ranges must be the same size.
FORMULA SYNTAX
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Argument | Description |
|---|---|
criteria_range1 | First range to check |
criteria1 | Condition to apply to the first range |
criteria_range2 | (optional) Another range to check |
criteria2 | (optional) Condition for the second range |
EXAMPLE 1: ONE CONDITION
Count how many rows have Region = West.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Product | Sales |
| 2 | West | B | 150 |
| 3 | East | A | 100 |
| 4 | West | A | 200 |
| 5 | West | B | 120 |
=COUNTIFS(A2:A5, "West")Result: 3 (rows 2, 4, and 5)
EXAMPLE 2: MULTIPLE CONDITIONS
Count how many rows have Region = West and Product = B.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Product | Sales |
| 2 | West | B | 150 |
| 3 | East | A | 100 |
| 4 | West | A | 200 |
| 5 | West | B | 120 |
=COUNTIFS(A2:A5, "West", B2:B5, "B")Result: 2 (rows 2 and 5)
COMMON PITFALLS AND EDGE CASES
- All criteria ranges must be the same size — mismatched ranges return
#VALUE! - If no rows match the criteria, the result is
0(not an error) - Text and logical operators must be enclosed in quotes, e.g.
"West",">100" - Blank cells are counted if they meet the criteria — use
"<>"to exclude blanks - Use wildcards like
"*"(any characters) and"?"(single character) in text criteria - COUNTIFS uses AND logic — all conditions must be true for a row to be counted
FURTHER READING
For more usage examples and documentation, see:
COUNTIFS – Microsoft Support