How to Use COUNTIF with Wildcard in Excel (7 Easy Ways)

When you want to specify partial criteria such as “begins with”, “ends with” or “contains” then wildcards are useful to do that in Excel. This article will guide you on how to use COUNTIF with Wildcard characters in excel with 7 easy methods.

7 Easy Methods to Use COUNTIF with Wildcard in Excel

We have taken a concise initial dataset to explain the steps clearly. The dataset has approximately 8 rows and 2 columns. Initially, we are keeping all the cells in General format. For the initial dataset, we have 2 unique columns which are Product Code and Quantity. Although we may vary the number of columns later if that is needed.

Method 1: Use COUNTIF with Wildcard in Excel to Specify Text Values

Let’s get introduced to our dataset first. Here, I have placed some products’ codes and quantities within 2 columns and 8 rows. Now I’ll use COUNTIF Wildcard to count the cells where there are text values. The COUNTIF function is used to count cells in a range that meets a single condition. And a Wildcard is a special character that lets you perform matching on text in your Excel formulas.

Steps:

• First, activate Cell C13.
• Then, type the formula given below-

• After that hit the Enter button to get the result.

Method 2: Use COUNTIF with Wildcard to Specify Numeric Values Only in Excel

In this method, we’ll use COUNTIF Wildcard to count the cells where there are numeric values.

Steps:

• Firstly, write the formula in Cell C13–

• At last, press the Enter button to get the result.

Method 3: Insert COUNTIF “Starts with” Wildcard in Excel

Now we’ll apply the COUNTIF Wildcard to count the cells where the values start with the characters “GHY”.

Steps:

• To begin with, in Cell C13 write the formula given below-

• Then click the Enter button and you will get the result.

Method 4: Apply COUNTIF “Ends with” Wildcard in Excel

Here, we’ll count the cells that end with the characters “GH” by using COUNIF Wildcard.

Steps:

• By activating Cell C13 type the given formula-

• Then just press the Enter button.

Method 5: Use COUNTIF “Contains” Wildcard in Excel

We’ll count the cells in this method that contain the value “256124FK”.

Steps:

• Write the formula in Cell C13–

• Hit the Enter button to get the counted result.

Method 6: Apply COUNTIF “?” Wildcard in Excel

The “?” Wildcard will allow you to specify any character in that position, for example, “HJI???GH” will search for values that start with HJI and end with GH but have any characters in positions 4, 5, and 6.

Steps:

• Write the formula in Cell C13 after activating it-

• Click the Enter button.

Method 7: Insert COUNTIF “~ (tilde)” Character Wildcard in Excel

The tilde character will be helpful if you want to search for the wildcard characters “?” and * as part of your criteria. For example  “*~?*” will find any values that contain a question mark.

Steps:

• Type the formula in Cell C13 which is given below-

• Finally, just press Enter button.

Read More: INDEX MATCH Multiple Criteria with Wildcard in Excel

Download Practice Workbook

You can download the free Excel template from here and practice on your own.

Conclusion

I hope all of the methods described above will be effective enough to use countif with a wildcard in Excel. Feel free to ask any questions in the comment section and please give me feedback.

Further Readings

• How to Find * Character Not as Wildcard in Excel 
• Excel VBA: Delete Files with Wildcards 
• SUMIFS with Wildcard in Excel
• How to Find And Replace Values Using Wildcards in Excel