## 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

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

### Remarks

It can contain numbers, a named range, arrays, or references containing numbers.*Range:*It can be a number, cell reference, expression, or text string.*Criteria:***COUNTIF**also supports the**wildcard characters (?)**and (***).**You can use the**m**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***, and**

*unit price***:**

*amount*A | B | C | D | E | F | |

1 | Item Code | Item Name | Date | Qty | Unit Price ($) | Amount ($) |

2 | Item01 | HP Desktop PC 1 | 16/3/2019 | 2 | 210 | 420 |

3 | Item01 | HP Desktop PC 1 | 17/3/2019 | 5 | 210 | 1050 |

4 | Item02 | HP Desktop PC 2 | 18/3/2019 | 1 | 250 | 250 |

5 | Item03 | HP Desktop PC 3 | 18/3/2019 | 3 | 600 | 1800 |

6 | Item01 | HP Desktop PC 1 | 19/3/2019 | 4 | 210 | 840 |

7 | Item04 | HP Desktop PC 4 | 19/3/2019 | 3 | 230 | 690 |

8 | Item01 | HP Desktop PC 1 | 20/3/2019 | 6 | 210 | 1260 |

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