Learn how to use the COUNTIF function in Excel to count cells based on criteria. Includes syntax, examples, and tips for accurate data filtering.
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
=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 |