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

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel COUNTIF Wildcard


Download Practice Workbook

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


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.

Dataset Overview


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-
=COUNTIF(B5:B11,"*")
  • 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
=COUNTIF(B5:B11,"<>*")
  • At last, press the Enter button to get the result.

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:

  • To begin with, in Cell C13 write the formula given below-
=COUNTIF(B5:B11,"GHY*")
  • Then 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.


Similar Readings


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.

Read More: INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)


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

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo