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.


Download Practice Book

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


7 Easy Methods to Use COUNTIF with Wildcard in Excel

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.

COUNTIF with Wildcard in Excel to Specify Text Values

Steps:

➤ Activate Cell C13

➤ Type the formula given below-

=COUNTIF(B5:B11,"*")

➤ Then 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:

➤ Write the formula in Cell C13

=COUNTIF(B5:B11,"<>*")

➤ Press the Enter button then.

COUNTIF with Wildcard to Specify Numeric Values Only in Excel


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:

➤ In Cell C13 write the formula given below-

=COUNTIF(B5:B11,"GHY*")

➤ Click the Enter button and you will get the result.

COUNTIF “Starts with” Wildcard in Excel


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-

=COUNTIF(B5:B11,"*GH")

➤ 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

=COUNTIF(B5:B11,"*256124FK*")

➤ Hit the Enter button to get the counted result.

COUNTIF “Contains” Wildcard in Excel


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-

=COUNTIF(B5:B11,"*HJI???GH")

➤ Click the Enter button.

COUNTIF “?” Wildcard in Excel


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-

=COUNTIF(B5:B11,"*~?*")

➤ Finally, just press Enter button.


Conclusion

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


Related Articles

How to Perform VLOOKUP with Wildcard in Excel (2 Methods)

How to Find And Replace Values Using Wildcards in Excel

Excel Advanced Filter [Multiple Columns & Criteria, Using Formula & with Wildcards]

How to Apply COUNTIF Not Equal to Text or Blank in Excel

The COUNTIF Function to Count Blank Cells in Excel: 2 Examples

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Excel is an amazing softwear. Here i will post excel related useful articles. I am a graduate from Bangladesh University of Engineering and Technology. I love to learn new things and work with it. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo