The COUNTIFS function counts how many rows match all the criteria you provide. This guide explains how COUNTIFS works and includes practical examples you can use immediately.
WHAT THE COUNTIFS FUNCTION DOES
COUNTIFS tallies rows that meet multiple conditions simultaneously, using AND logic — all criteria must be satisfied for a row to be counted. It's like COUNTIF, but supports multiple filters, such as counting orders where the region is West and the product is B.
WHEN TO USE COUNTIFS
Use COUNTIFS when you want to:
- Count values using multiple filters
- Apply multiple conditions to your data
- 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)
Need help applying this? Try this in Numstro Builder
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)
Need help applying this? Try this in Numstro Builder
COMMON MISTAKES & 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
RELATED FUNCTIONS
- COUNTIF — count with one condition
- SUMIFS — sum with multiple conditions
- AVERAGEIFS — average with multiple conditions
FURTHER READING
Official Microsoft documentation:
COUNTIFS — Microsoft Support