Countif

 

Numbers | Text | Booleans | Or Criteria

The powerful COUNTIF function in Excel counts cells based on one criteria. This page contains many easy to follow COUNTIF examples.

Numbers

1. The COUNTIF function below counts the number of cells that contain the value 20.

Simple Countif Function in Excel

2. The following COUNTIF function gives the exact same result. It counts the number of cells that are equal to the value in cell C1.

Count Cells Equal to Cell Value

3. The COUNTIF function below counts the number of cells that are greater than or equal to 10.

Count Cells Greater Than Or Equal to Value

4. The following COUNTIF function gives the exact same result. It uses the & operator to join the 'greater than or equal to' symbol and the value in cell C1.

Countif Function and the Ampersand Operator

5. The COUNTIF function below counts the number of cells that are not equal to 7.

Count Cells Not Equal to Value

6. The COUNTIF functions below count the number of cells that are equal to 3 or 7.

Two Countif Functions in Excel

7. The COUNTIF function below counts the number of cells that are less than the average of the values (8.2).

Countif Function and the Average Function

Text

1. The COUNTIF function below counts the number of cells that contain exactly star.

Countif Function in Excel, Exactly

2. The COUNTIF function below counts the number of cells that contain exactly star + 1 character. A question mark (?) matches exactly one character.

Countif Function, Question Mark

3. The COUNTIF function below counts the number of cells that contain exactly star + a series of zero or more characters. An asterisk (*) matches a series of zero or more characters.

Countif Function, Asterisk

4. The COUNTIF function below counts the number of cells that contain star in any way. No matter what is before or after star, this function finds all the cells that contain star in any way.

Count All Occurrences

5. The COUNTIF function below counts the number of cells that contain text.

Count Cells That Contain Text

Booleans

1. The COUNTIF function below counts the number of cells that contain the Boolean TRUE.

Count TRUE

2. The COUNTIF function below counts the number of cells that contain the Boolean FALSE.

Count FALSE

3. The COUNTIF functions below count the number of cells that contain the Boolean TRUE or FALSE.

Count TRUE or FALSE

Or Criteria

Counting with Or criteria in Excel can be tricky.

1. The COUNTIF functions below count the number of cells that contain Google or Facebook (one column).

Simple Or Criteria

2. However, if you want to count the number of rows that contain Google or Stanford (two columns), you cannot simply use the COUNTIF function twice (see the picture below).

Wrong Formula

Note: rows that contain Google and Stanford are counted twice, but they should only be counted once. 4 is the answer we are looking for.

3. The array formula below does the trick.

Count with Or Criteria in Excel

Note: finish an array formula by pressing CTRL + SHIFT + ENTER. Excel adds the curly braces {}. Visit our page about Counting with Or Criteria for detailed instructions on how to create this array formula and become an Excel pro.

4. Counting with And criteria is a lot easier. For example, to count the number of rows that contain Google and Stanford, simply use the COUNTIFS function (with the letter S at the end).

Countifs Function in Excel

Note: The COUNTIFS function in Excel counts cells based on two or more criteria.