## How to Use the COUNTIF Function and Formula in Excel

In this tutorial, you'll learn how to use the **COUNTIF function** and formula in Excel. **COUNTIF **is a function to count the number of cells in a range of cells that meet a single criterion, and you also 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 used to determine which cells you want to count.*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 them in the criteria 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 table is a demo data that includes **item code**, **item name**, **quantity**, **unit price**, and the** amount** we're going to use in our examples.

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

In this example, let's say you want to count the number of cells that values are equal to "**HP DESKTOP PC 1**" in the range A2:F2.

The **COUNTIF** formula will look like this:

=

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

**COUNTIF** counts the number of cells that values are equal to "**HP DESKTOP PC 1**" in the range A2:F8 and returns 4.

#### Example 2: Using Logical Operator (<=) in Criteria

In this example, I show you how you use the logical operator (**<=**) in criteria.

For example, let's say you want to count the number of cells that the unit price is less than or equal to 250 in the range E2:E8, then you can write the COUNTIF formula as follows:

=

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

**COUNTIF** returns 6.

#### Example 3: Using Logical Operator (>) in Criteria

In this example, I show you another formula that uses the logical operator (**>**) in the criteria.

Let's look at the COUNTIF formula below.

=

COUNTIF(E2:E8,">700")

**COUNTIF** counts the number of cells that the unit price is greater than (**>**) 700 in the range E2:E8 and returns 0. So, 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

In this example, I show you how to use the wildcard character "*" in the criteria.

Now, let's take a look at an example:

=

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

In the above formula, we placed * before PC 1. Since the wildcard character (*) matches any sequence of characters in Excel, **COUNTIF** counts the number of cells that the item name begins with any text and ends with **PC 1** in the range A2:F8. The formula returns 4.

#### Example 5: Using the Wildcard Character (?) in Criteria

In this example, I also show you how to use another wildcard character (?) in criteria.

To use the wildcard (**?**) in criteria, you can write the COUNTIF formula as follows:

=

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

In the above formula, as you can see, there are 4 wildcard characters (**?**) in the criteria.

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

**COUNTIF** 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. The formula returns 7.

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

Now the formula will look like this:

=

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

**COUNTIF** returns 7. As you can see in the sample data above, no cells are containing **hp desktop**. **COUNTIF** is not case-sensitive, so the lower and upper case characters in text string are treated the same. In this case, "**HP Desktop**" and "**hp desktop**" are considered to be the same.

If you want to count only cells containing numbers, use theCOUNTfunction, and useCOUNTAto count only cells that are not blank. If you want to count the blank cells, useCOUNTBLANK, and use theCOUNTIFSfunction to count cells that meet multiple criteria.

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. But, if you need to count the number of cells that meet multiple criteria, use the **COUNTIFS** function.