[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.

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(?).

How to Fix If COUNTIF Function with Wildcard Is Not Working in Excel: 4 Reasons and Possible Solutions

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: How to Compare Two Columns Using COUNTIF Function

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: How to Use COUNTIF Function to Count Text from List in Excel

Similar Readings

• Count Text at Start with COUNTIF & LEFT Functions in Excel
• How to Use Excel COUNTIF Between Time Range
• How to Use COUNTIF to Count Date Less Than Today in Excel
  
• COUNTIF Function to Count Cells That Are Not Equal to Zero

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 Function In Excel to Count Bold Cells

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 Function to Calculate Percentage in Excel

Things to Remember

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

Download Practice Workbook

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

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. Stay with us and keep learning.

Related Articles

• How to Use COUNTIF for Non Contiguous Range in Excel
• Excel COUNTIF to Count Cell That Contains Text from Another Cell
• Excel COUNTIF to Count Cells Greater Than 1
• How to Use COUNTIF Function in Excel Greater Than Percentage
• How to Use COUNTIF Function with Array Criteria in Excel
• How to Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel
• How to Use the Combination of COUNTIF and SUMIF in Excel
• Difference Between SUMIF and COUNTIF Functions in Excel
• How to Use IF and COUNTIF Functions Together in Excel
• How to Use Nested COUNTIF Function in Excel
• How to Use COUNTIF and COUNTA Functions Together in Excel
• How to Calculate Frequency Using COUNTIF Function in Excel
• Excel COUNTIF Function with Conditional Formatting
• [Solved!]: Excel COUNTIF Returning 0 Instead of Actual Value
• [Fixed!] Excel COUNTIF Function Not Working for String “True”