Wildcards are useful to specify partial criteria such as “begins with”, “ends with” or “contains”.
The sample dataset has 8 rows and 2 columns. All cells are in General format.
Method 1 – Use the COUNTIF function with a Wildcard to Specify Text Values in Excel
The COUNTIF function is used to count cells in a range that meet a single condition. To count cells with text values:
Steps:
- Select C13.
- Enter the formula below.
=COUNTIF(B5:B11,"*")- Press Enter to see the result.
Method 2 – Using the COUNTIF with a Wildcard to Specify Numeric Values Only in Excel
The COUNTIF Wildcard will count cells with numeric values.
Steps:
- Enter the formula in C13.
=COUNTIF(B5:B11,"<>*")- Press Enter to see the result.
Method 3 – Inserting the COUNTIF “Starts with” Wildcard in Excel
To count the cells in which the values start with “GHY”:
Steps:
- Enter the formula in C13.
=COUNTIF(B5:B11,"GHY*")- Press Enter to see the result.
Method 4 – Applying the COUNTIF “Ends with” Wildcard in Excel
To count the cells that end with the characters “GH”:
Steps:
- Enter the formula in C13.
=COUNTIF(B5:B11,"*GH")- Press Enter to see the result.
Method 5 – Using the COUNTIF “Contains” Wildcard in Excel
Tocount the cells that contain the value “256124FK”:
Steps:
- Enter the formula in C13.
=COUNTIF(B5:B11,"*256124FK*")- Press Enter to see the result.
Method 6 – Applying the COUNTIF “?” Wildcard in Excel
The “?” Wildcard allows you to specify any character in that position. “HJI???GH” will search for values that start with HJI and end with GH.
Steps:
- Enter the formula in C13.
=COUNTIF(B5:B11,"*HJI???GH")- Press Enter to see the result.
Method 7 – Inserting the COUNTIF “~ (tilde)” Character Wildcard in Excel
The tilde character can be used to find the wildcard characters “?” and * as part of the criteria. “*~?*” will find values containing a question mark.
Steps:
- Enter the formula in C13.
=COUNTIF(B5:B11,"*~?*")- Press Enter to see the result.
Read More: INDEX MATCH Multiple Criteria with Wildcard in Excel
Download Practice Workbook
Download the free Excel template here and practice.
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
