COUNTIFS function
COUNTIFS counts the rows where all of the listed conditions hold at once, such as orders from one region in a given month. It is the standard way to do AND-style counting.
Example
Read it as a sentence: count the rows where M2:M7 (Region) is North and N2:N7 (Product) is A, which gives 2.
Excel shows 2
M
N
1
Region
Product
2
North
A
3
South
B
4
North
B
5
East
A
6
North
A
7
South
A
Data
Syntax
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Arguments in [square brackets] are optional.
-
criteria_range1(required) - First range to test.
-
criteria1(required) - Condition for the first range.
-
criteria_range2(optional) - Optional additional range.
-
criteria2(optional) - Condition for the additional range.
Returns: How many rows meet every condition.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=COUNTIFS(Data!M2:M7,"North",Data!N2:N7,"A") | 2 | Two criteria |
=COUNTIFS(Data!M2:M7,"North",Data!O2:O7,">60") | 2 | Numeric + text |
=COUNTIFS(Data!M2:M7,"West") | 0 | No match |
=COUNTIFS(Data!M2:M7,{"North";"South"},Data!N2:N7,"A") | 2 | Array criteria spill |
=COUNTIFS(Data!M2:M7,"?ast") | 1 | Wildcard ? |
=COUNTIFS(Items!C2:C13,"Tools",Items!I2:I13,FALSE) | 3 | Site: featured, uninsured tools |
=COUNTIFS(Data!M2:M7,"North",Data!N2:N5,"A") | #VALUE! | Mismatched range sizes |
=COUNTIFS(Items!C2:C13,"Tools",Items!I2:I12,FALSE) | #VALUE! | Site: ranges of different sizes |
The Data sheet these formulas read
A cell that starts with = holds that formula.
M
N
O
1
Region
Product
Sales
2
North
A
100
3
South
B
150
4
North
B
200
5
East
A
6
North
A
50
7
South
A
75
Data
The Items sheet these formulas read
C
I
1
Category
Insured
2
Tools
FALSE
3
Smoking
FALSE
4
Household
FALSE
5
Smoking
FALSE
6
Smoking
FALSE
7
Papers
FALSE
8
Tools
FALSE
9
Clothing
FALSE
10
Household
FALSE
11
Tools
FALSE
12
Tools
TRUE
13
Chemistry
TRUE
Items
Watch out for
- All ranges must be the same size.
- Conditions combine with AND; for OR add separate COUNTIFS together.
- Needs real ranges.
- Excel refuses the formula outright if you type an array constant such as {1,2,3} where a range is expected; point it at cells instead (measured in Excel 16.0.20527).
Where it works
- Excel
- COUNTIFS is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .