[Fixed] COUNTIF Function with Wildcard Not Working in Excel

The COUNTIF function is used extensively to count values based on criteria. Using wildcards in the criteria makes the formula more dynamic. Unfortunately, sometimes it may seem that the Excel COUNTIF formula with wildcard is not working as expected. In this article, we will highlight some of the possible reasons for this and the solutions to fix those issues.

Download Practice Workbook

You can download the practice workbook from the download button below.

How to Use COUNTIF Function with Wildcard in Excel

In this section, we will highlight how to use wildcards in the criteria argument of the COUNTIF function.

• Firstly, you can use asterisks(*) enclosed with double quotes to count texts values only

• Secondly, you can use greater than – less than with asterisks to count numbers only.

• Thirdly, you can use some characters before asterisks to count values that start with those characters.

• Thirdly, you can use some characters after asterisks to count values that end with those characters.

• Fifthly, you can use asterisks before and after any text to count values containing the text.

• Sixthly, you can use question marks(?) to count values with any characters in the specified positions.

• Seventhly, you can use a tilde(~) to count values with other wildcards. For example, using “*~?*” will give the count of values containing question marks(?).

4 Reasons and Possible Solutions If COUNTIF Function with Wildcard Is Not Working in Excel

Here we will highlight 3 possible reasons and solutions for the COUNTIF formula with wildcard not working in Excel. So let’s start!

1. COUNTIF Function with Wildcard Is Not Working in Case of Cell Reference

Consider the following COUNTIF formula returning 0 instead of 5.

• The formula contains asterisks (*) as wildcards to count the matching contents referenced by cell D5. Here, the COUNTIF function does not consider D5 used in the criteria argument as a cell reference but rather as text. Because the cell reference is enclosed with double quotes. That’s why the COUNTIF function is looking for texts containing D5 instead of the contents of cell D5. As a result, it returns 0.
• You can modify the formula to fix the issue using the ampersand (&) symbol as follows so that the cell reference is outside double quotes. Then the COUNTIF formula will return the accurate result.

Read More: COUNTIF Excel Example (22 Examples)

2. COUNTIF Function with Wildcard Is Not Working for Texts

Now consider the following COUNTIF formula returning 0 instead of 4.

• The formula looks for the characters SB at the beginning of the texts. Here, the problem is that the seemingly matching texts contain leading spaces.

• You need to remove the leading or trailing spaces to make the COUNTIF formula work properly. Then you will get the accurate results as follows.

Read More: VBA COUNTIF Function in Excel (6 Examples)

Similar Readings

• COUNTIF Date Is within 7 Days
• How to Use Excel COUNTIF That Does Not Contain Multiple Criteria
• Count Blank Cells with Excel COUNTIF Function: 2 Examples
• COUNTIF Between Two Dates in Excel (4 Suitable Examples)
• Apply COUNTIF Function in Multiple Ranges for Same Criteria

3. COUNTIF Function with Wildcard Is Not Working in Case of Dates

Furthermore, the COUNTIF function with wildcard cannot return the expected output in the case of dates. If you look at the following formula and the image, you’ll get that the following COUNTIF formula returns 0.

• Here the purpose of the COUNTIF formula is to count the dates from the month of September 2016. But you cannot use wildcards with dates in this way.
• Instead, you can use the COUNTIFS function along with the DATE function to get the desired result here. Apply the following formula to do that.

• Here, the DATE function has been used to check for dates in the criteria_range that are greater than or equal to 11/1/2016 and less than 12/1/2016.

Read More: How to Use COUNTIF for Date Range in Excel (6 Suitable Approaches)

4. COUNTIF Function with Wildcard Is Not Working in the Case of Numbers

Suppose you are trying to enter the following formula to count the number of cells containing 1.

Then the following error will occur.

This is because the wildcards are not enclosed with double quotes. You always need to enclose the wildcards with double quotes even if you are using them with numbers.

Read More: How to Use COUNTIF Between Two Numbers (4 Methods)

Things to Remember

• You have to use the wildcards inside double quotes.
• Using wildcards in the wrong way will return inaccurate results.

Conclusion

At this moment, you know how to fix this issue in Excel if the COUNTIF formula with wildcard is not working. Do you have any further queries or suggestions? Please let us know using the comment section below. You can also visit our ExcelDemy blog to explore more about Excel. Stay with us and keep learning.

Related Articles

• COUNTIF Function to Count Cells That Are Not Equal to Zero
• How to Compare Two Columns Using COUNTIF Function (4 Ways)
• Use COUNTIF Function in Excel Greater Than Percentage
• How to Use COUNTIF to Count Date Less Than Today in Excel
• COUNTIF vs COUNTIFS in Excel (4 Examples)
• How to Use COUNTIF with SUBTOTAL in Excel (2 Methods)
• Use COUNTIF with WEEKDAY in Excel (2 Easy Methods)