How to Use COUNTIF Function in Excel | Excel Formula Guide
Learn how to use the COUNTIF function in Excel to count cells based on criteria. Includes syntax, examples, and tips for accurate data filtering.
COUNTIF function
Use COUNTIF, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.
Syntax
COUNTIF(range, criteria)For example:
=COUNTIF(A2:A5,"London")
=COUNTIF(A2:A5,A4)see also:
Exercise : Counting with Logical Operators
- Open a new Excel worksheet.
- Create a list of test scores in column A (e.g., A1: 85, A2: 92, A3: 78, A4: 65, A5: 99).
- In cell B1, use the COUNTIF function to count the number of scores greater than or equal to 90.
- The formula should be
=COUNTIF(A1:A5, ">=90").
Exercise : Use the COUNTIF function to count the number of "Apples" in the list:
| Column A |
|---|
| Bananas |
| Apples |
| Oranges |
| Apples |
| Grapes |
| Strawberries |
| Apples |
Exercise : In the following table, count the number of cells in column B that contain the value "100".
| Column A | Column B |
|---|---|
| Product A | 100 |
| Product B | 200 |
| Product C | 300 |
| Product D | 100 |
Exercise : In the following table, count the number of cells in column B that contain a value that contains the letter "a":
| Column A | Column B |
|---|---|
| Product A | Apple |
| Product B | Banana |
| Product C | Carrot |
| Product D | Orange |
Exercise : In the following table, count the number of cells in column B that contain a value that starts with the letter "a":
| Column A | Column B |
|---|---|
| Product A | Apple |
| Product B | Banana |
| Product C | Carrot |
| Product D | Orange |
Exercise : In the following table, count the number of cells in column B that contain a value that ends with the letter "e":
| Column A | Column B |
|---|---|
| Product A | Apple |
| Product B | Banana |
| Product C | Carrot |
| Product D | Orange |