The** COUNTIF** function counts the number of cells within a given data range that meet specified criteria or conditions.

The **COUNTIF** function can also return the number of cells that contain text values that partially match the criteria value.

We will use the following dataset to demonstrate how the **COUNTIF** function can be used to count cells that have values that partially match the criteria value:

The dataset shows the product details of 11 products.

Our dataset is small for demonstration purposes but in a practical situation, you will normally work with very large datasets.

**Use of wildcards in Excel**

Wildcards play such an important role when we use the **COUNTIF** function to return the count of partial matches. Wildcards are the special characters that can take the place of any character.

There are only three wildcard characters that are used in Excel:

**?****(Question mark)**. It takes the place of only one character. For instance**Cor?**could return Cork or Core.*** (Asterisk)**. It takes the place of one or more characters. For instance, Con* could return Conman, Contrary, or Contract.**~****(tilde)**. It is used to show the question mark (?) and asterisk (*) characters. For instance**Cor~?**would return Cor? and**Con~***would return Con*.

The most commonly used wildcard characters in Excel are the Question mark (?) and the asterisk (*).

It is important to note that these wildcard characters work only with text values and not numbers.

**Examples of Uses of COUNTIF in Partial match **

**Example 1 **– How to return the count of Product Category names that contain the word phone:

Step 1 – Enter the word “phone” in cell **G2**. This is the criteria value:

Step 2 – Enter the formula **=COUNTIF(C2:C11,”*”&G2&”*”) **in cell **G3**:

Step 3 – Press the **Enter** key:

The **COUNTIF** function returned the value of 1 meaning there is only one partial match in the data range, which in this case is the Telephone Category.

Explanation of the **=COUNTIF(C2:C11,”*”&G2&”*”)** formula:

The **COUNTIF** function has the two arguments:

**C2:C11**is the data range in which the function will look for partial matches.**“*”&G2&”*”**is the criteria or condition for the partial match. See that cell**G2**which contains the criteria value is flanked by asterisks (*) on both sides. Also, note that the asterisks are in double-quotes and joined to cell**G2**using the**concatenating character (&**).

**Changing Criteria**

We can change the criteria value in cell **G2** and the **COUNTIF** function will return the correct count of partial matches. For example, let’s change the criteria value to Cartridge:

The count has been updated accordingly.

**COUNTIFS function**

The **COUNTIF** function we have so far used can only return the number of cells that meet one condition. If we want to return the count of cells that meet more than one condition, we have to use the **COUNTIFS** function.

**Example 2** – How to return the count of Product Category names that contain the words Accessory and Product:

Step 1- Enter the word Accessory in cell **G2** and the word Product in cell **H2**:

Step 2 – Enter the formula **=COUNTIFS(C2:C11,”*”&G2&”*”,C2:C11,”*”&H2&”*”) **in cell **G2**:

Step 3 – Press the Enter key:

We get the count of 1 because only the Accessory/IT Product Category meets both conditions.

Explanation of the **=COUNTIFS(C2:C11,”*”&G2&”*”,C2:C11,”*”&H2&”*”) **formula:

This formula has 2 conditions for the partial matches: **“*”&G2&”*” **and **“*”&H2&”*”**.

The **COUNTIFS** function looks for the partial matches in the same data range **C2:C11** for both conditions or criteria.

**Conclusion**

In this tutorial, we have looked at how one can use the **COUNTIF **and **COUNTIFS** functions to return the count of cells that contain values that partially meet certain conditions.