COUNTIF function
COUNTIF returns the number of cells matching one 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
Count open records. Put comparison operators inside quotes.
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 |
=COUNTIF(B7:B11,"Open")Expected result2
See the inputs and instructions for all 3 examplesSyntax
COUNTIF(range, criteria)
Copyable formulas
=COUNTIF(B2:B5,C2)=EĞERSAY(B2:B5;C2)Sample data
Enter the headers and sample records in A1:B5.
| Task | Status |
|---|---|
| Count | Open |
| Delivery | Closed |
| Report | Open |
| Review |
- C2 = Open
Enter the formula in D2.
To try the Turkish formula, use the sample data on the Turkish page.
Expected result
2
How it works
The range is B2:B5. The blank status does not match Open. Change C2 to Closed and the result is 1. Use COUNTIFS for two separate conditions.
Syntax
COUNTIF(range, criteria)
Compatibility
Excel for Microsoft 365 and Excel 2016, 2019, 2021, and 2024.
Related pages
Worked examples
Count open records. Put comparison operators inside quotes.
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 |
|---|---|---|
| Open | =COUNTIF(B7:B11,"Open") |
2 |
| Paid | =COUNTIF(B7:B11,"Paid") |
3 |
| Amount ≥ 80 | =COUNTIF(C7:C11,">=80") |
2 |