Statistical
Excel 2007
◉ 27 views
=COUNTIFS()
How to use the COUNTIFS function in Excel to apply criteria to cells, across multiple ranges, and count the number of times all criteria are met.
Applies To:
Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for MacExcel 2019Excel 2016Excel Web App
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
Hover argument names in the table for details.
Output typeNumber
Popularity70 / 100
VolatileNo
Dynamic ArrayNo
📥 Download sample .xlsx ▦ Try in Playground
Dedicated file for this function — safe & lightweight · sample data + live formulas
⬇ Download Sample Workbook
A dedicated .xlsx for =COUNTIFS() with Persian sample data & live formulas — open in Excel and practice.
- Sheet “Sample” — data table + formula in place
- Sheet “Guide” — syntax, arguments & tips from this page
- Instant & secure (token + rate limit)
🧠 🧠 Quiz this function
%d questions — syntax, example & errors — test yourself!
◧ Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
criteria_range1 |
Any type | Required | The first range in which to evaluate the associated criteria. |
criteria1 |
Any type | Required | The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32". |
criteria_range2 |
Any type | Optional | , criteria2, ... Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed. |
▣ Examples
▦ Data
| Cell | Value |
|---|---|
A2 |
Salesperson |
B2 |
Exceeded Q1 quota |
C2 |
Exceeded Q2 quota |
D2 |
Exceeded Q3 quota |
A3 |
Davidoski |
B3 |
Yes |
C3 |
No |
D3 |
No |
A4 |
Burke |
B4 |
Yes |
C4 |
Yes |
D4 |
No |
A5 |
Sundaram |
B5 |
Yes |
C5 |
Yes |
D5 |
Yes |
A6 |
Levitan |
B6 |
No |
C6 |
Yes |
D6 |
Yes |
A7 |
1 |
B7 |
5/1/2011 |
A8 |
2 |
B8 |
5/2/2011 |
A9 |
3 |
B9 |
5/3/2011 |
A10 |
4 |
B10 |
5/4/2011 |
A11 |
5 |
B11 |
5/5/2011 |
A12 |
6 |
B12 |
5/6/2011 |
A13 |
Salesperson |
B13 |
Exceeded Q1 quota |
C13 |
Exceeded Q2 quota |
D13 |
Exceeded Q3 quota |
A14 |
Davidoski |
B14 |
Yes |
C14 |
No |
D14 |
No |
A15 |
Burke |
B15 |
Yes |
C15 |
Yes |
D15 |
No |
A16 |
Sundaram |
B16 |
Yes |
C16 |
Yes |
D16 |
Yes |
A17 |
Levitan |
B17 |
No |
C17 |
Yes |
D17 |
Yes |
A18 |
1 |
B18 |
5/1/2011 |
A19 |
2 |
B19 |
5/2/2011 |
A20 |
3 |
B20 |
5/3/2011 |
A21 |
4 |
B21 |
5/4/2011 |
A22 |
5 |
B22 |
5/5/2011 |
A23 |
6 |
B23 |
5/6/2011 |
Counts how many times Davidoski exceeded a sales quota for periods Q1, Q2, and Q3 (only in Q1).
=COUNTIFS(B2:D2,"=Yes")
Result:
1
Counts how many salespeople exceeded both their Q1 and Q2 quotas (Burke and Sundaram).
=COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes")
Result:
2
Counts how many times Levitan and Burke exceeded the same quota for periods Q1, Q2, and Q3 (only in Q2).
=COUNTIFS(B5:D5,"=Yes",B3:D3,"=Yes")
Result:
1
Counts how many numbers between 1 and 6 (not including 1 and 6) are contained in cells A2 through A7.
=COUNTIFS(A2:A7,"<6",A2:A7,">1")
Result:
4
Counts how many rows have numbers that are less than 5 in cells A2 through A7, and also have dates that are are earlier than 5/3/2011 in cells B2 through B7.
=COUNTIFS(A2:A7, "<5",B2:B7,"<5/3/2011")
Result:
2
Same description as the previous example, but using cell references instead of constants in the criteria.
=COUNTIFS(A2:A7, "<" & A6,B2:B7,"<" & B4)
Result:
2
§ Remarks
- Each range's criteria is applied one cell at a time. If all of the first cells meet their associated criteria, the count increases by 1. If all of the second cells meet their associated criteria, the count increases by 1 again, and so on until all of the cells are evaluated.
- If the criteria argument is a reference to an empty cell, the COUNTIFS function treats the empty cell as a 0 value.
- You can use the wildcard characters— the question mark (?) and asterisk (*) — in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
✦ Tips
- Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other.
- Each range's criteria is applied one cell at a time. If all of the first cells meet their associated criteria, the count increases by 1. If all of the second cells meet their associated criteria, the count increases by 1 again, and so on until all of the cells are evaluated.
- If the criteria argument is a reference to an empty cell, the COUNTIFS function treats the empty cell as a 0 value.
- You can use the wildcard characters— the question mark (?) and asterisk (*) — in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
💬 Comments & Q&A 0 approved
🔒 Your comment will be published after review.Share your thoughts — visible after admin approval
No comments yet — be the first!