COUNTIFS function
COUNTIFS counts rows that meet every supplied criterion.
Statistical
Where can you use it?
Analytics, quality and research teams. Use this group to examine distributions, variability or relationships in measurements, sales or service durations.
Scenario in the workbook
Check region, status and amount on the same row. All criteria ranges must have the same dimensions.
A worked example from the workbook
| Row | A | B | C |
|---|---|---|---|
| 6 | Region | Status | Amount |
| 7 | North | Paid | 120 |
| 8 | South | Paid | 75 |
| 9 | North | Open | 40 |
| 10 | North | Paid | 80 |
| 11 | South | Open | 0 |
=COUNTIFS(A7:A11,"North",B7:B11,"Paid")Expected result2
See the inputs and instructions for all 3 examplesSyntax
COUNTIFS(criteria_range1, criteria1, …)
Copyable formulas
=COUNTIFS(A2:A5,D2,B2:B5,">="&D3)=ÇOKEĞERSAY(A2:A5;D2;B2:B5;">="&D3)Sample data
Enter the headers and sample records in A1:B5.
| Team | Hours |
|---|---|
| Warehouse | 8 |
| Field | 9 |
| Warehouse | 6 |
| Warehouse | 7 |
- D2 = Warehouse
- D3 = 7
Enter the formula in E2.
To try the Turkish formula, use the sample data on the Turkish page.
Expected result
2
How it works
To build a criterion from a threshold cell, quote the operator and append the reference with &. The threshold includes exactly seven hours. Raise it to eight and the count becomes 1.
Syntax
COUNTIFS(criteria_range1, criteria1, [criteria_range2], [criteria2], …)
Compatibility
Excel for Microsoft 365 and Excel 2016, 2019, 2021, and 2024.
Related pages
Worked examples
Check region, status and amount on the same row. All criteria ranges must have the same dimensions.
Open the English sheet. Inputs begin at A6. F7:F9 show the live results and G7:G9 show the original results for comparison.
| Row | A | B | C |
|---|---|---|---|
| 6 | Region | Status | Amount |
| 7 | North | Paid | 120 |
| 8 | South | Paid | 75 |
| 9 | North | Open | 40 |
| 10 | North | Paid | 80 |
| 11 | South | Open | 0 |
| Case | Formula | Expected result |
|---|---|---|
| North / Paid | =COUNTIFS(A7:A11,"North",B7:B11,"Paid") |
2 |
| South / Open | =COUNTIFS(A7:A11,"South",B7:B11,"Open") |
1 |
| North / ≥ 100 | =COUNTIFS(A7:A11,"North",C7:C11,">=100") |
1 |