Excel COUNTIF Function and Formula with Examples

How to Use the COUNTIF Function in Excel

In this tutorial, you'll learn how to use the Excel COUNTIF function. COUNTIF is a function to count the number of cells in a range of cells that meet a single criterion. You can use it to count cells that contain numbers, dates, or references containing numbers.

COUNTIF Syntax

The syntax of the COUNTIF function is as follows:

=COUNTIF(range, criteria)

Arguments

• Range: The range of cells that you want to count.
• Criteria: The criteria to use.

Remarks

• Range: It can contain numbers, a named range, arrays, or references containing numbers.
• Criteria: It can be a number, cell reference, expression, or text string.
• COUNTIF also supports the wildcard characters (?) and (*). You can use them for partial matching.
• COUNTIF ignores lower and upper case in text strings. So, for example, "Desktop" and "DESKTOP" are treated the same.

COUNTIF Excel Function Examples

The following demo data includes item code, item name, quantity, unit price, and amount:

Example 1: Using text criteria

Let's say you want to count the number of cells that values are equal to "HP DESKTOP PC 1" in the range A2: F8.

The COUNTIF formula will look like this:

=COUNTIF(A2:F8, "HP DESKTOP PC 1")

COUNTIF returns 4.

Example 2: Using logical operator "<=" in criteria

You can use a logical operator (<=) in the criteria.

For example, let's say you want to count the number of cells that the unit price column is less than or equal to 250 in the range E2: E8. 

You can write the COUNTIF formula as follows:

=COUNTIF(E2:E8, "<=250")

COUNTIF returns 6.

Example 3: Using logical operator (>) in criteria

You can also use the logical operator (>) in the criteria.

Here is an example:

=COUNTIF(E2:E8, ">700")

COUNTIF counts the number of cells that the unit price column that is greater than 700 in the range E2: E8. 

The formula returns 0. There are no cells that match the specified condition.

You also can use other logical operators such as greater than or equal to (>=) and less than (<).

Example 4: Using the wildcard character (*) in criteria

You can use the wildcard character (*) in the criteria.
Let's take a look at an example:

=COUNTIF(A2:F8,"*PC 1")

The character * placed before PC 1. 

COUNTIF counts the number of cells that the item name begins with any text and ends with PC 1 in the range A2: F8 because the wildcard character (*) matches any sequence of characters in Excel.

The formula returns 4. 

Example 5: Using the wildcard character (?) in criteria

There is another wildcard character (?) that you can use in the criteria.

You can use the wildcard (?) in criteria, as the following example shows:

=COUNTIF(A2:F8, "HP Desktop?????")

As you can see, there are four wildcard characters (?) in the criteria. 

The formula counts the number of cells that the item name begins with HP Desktop and ends with the text string of 5 characters in the range A2: F8 and returns 7.

Note that the wildcard character (?) matches any single character in the text string. 

Example 6: COUNTIF is not case sensitive

Let's take the previous formula in Example 5 and change all characters in the criteria to lower case.
The formula will look like this:

=COUNTIF(A2:F8, "hp desktop?????")

As you can see, no cells are containing hp desktop. However, since COUNTIF is not case-sensitive, the lower and upper case characters in text string are treated the same. 

"HP Desktop" and "hp desktop" are considered to be the same.

There are some other count functions, as listed below.

• If you want to count only cells containing numbers, use COUNT.
• If you want to count only cells that are not blank, use COUNTA. 
• If you want to count the blank cells, use COUNTBLANK. 
• To count cells that meet multiple criteria, use COUNTIFS.

In this tutorial, you've learned how to use the Excel COUNTIF function. COUNTIF is a function to count the number of cells in a range of cells that meet a single criterion.