Countif

Microsoft Excel provides several functions purposed for counting different kinds of cells, such as blanks or non-blanks, with number, date or text values, containing specific words or character, etc.

The Microsoft Excel COUNTIF function counts the number of cells in a range, that meets a given criteria.

This video will educate you on the basics of how to use the statistical function COUNTIF. The syntax of COUNTIF is =COUNTIF(Range, Criteria). Watch the quick video for an example of how to use.

This Excel tutorial explains how to use the Excel COUNTIF function with syntax and examples.

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

Countif with Numeric Criteria

You can use the COUNTIF function in Excel to count cells that contain a specific value, count cells that are greater than or equal to a value, etc.

1. For example, the COUNTIF function below counts the number of cells that contain the value 20.

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.

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

4. The following COUNTIF function gives the exact same result. The & operator joins the ‘greater than or equal to’ symbol and the value in cell C1.

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

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

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

Countif with Text Criteria

You can also use the COUNTIF function in Excel to count cells that contain specific text. Always enclose text in double quotation marks. You can even use wildcards.

1. For example, the COUNTIF function below counts the number of cells that contain exactly star.

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

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.

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.

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

Count Booleans

You can also use the COUNTIF function in Excel to count Boolean values (TRUE or FALSE).

1. For example, the COUNTIF function below counts the number of cells that contain the Boolean TRUE.

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

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

Count Errors

You can also use the COUNTIF function in Excel to count specific errors.

1. For example, count the number of cells that contain the #NAME? error.

2. The array formula below counts the total number of errors in a range of cells.

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

Or Criteria

Counting with Or criteria in Excel can be tricky.

1. The formula below counts the number of cells that contain Google or Facebook (one column).

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).

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.

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 Criteria

Counting with And criteria is a lot easier. The COUNTIFS function (with the letter S at the end) in Excel counts cells based on two or more criteria.

1. For example, to count the number of rows that contain Google and Stanford, simply use the COUNTIFS function.