[Fixed] COUNTIF Function with Wildcard Not Working in Excel

Get FREE Advanced Excel Exercises with Solutions!

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
=COUNTIF(B5:B15,"*")

Excel COUNTIF wildcard - Asterisks(*)

  • Secondly, you can use greater than – less than with asterisks to count numbers only.
=COUNTIF(B5:B15,"<>*")

wildcard with numbers only

  • Thirdly, you can use some characters before asterisks to count values that start with those characters.
=COUNTIF(B5:B15,"AB*")

 wildcard with starting characters

  • Thirdly, you can use some characters after asterisks to count values that end with those characters.
=COUNTIF(B5:B15,"*AB")

wildcard with ending characters

  • Fifthly, you can use asterisks before and after any text to count values containing the text.
=COUNTIF(B5:B15,"*BC*")

wildcard for containing characters

  • Sixthly, you can use question marks(?) to count values with any characters in the specified positions.
=COUNTIF(B5:B15,"H2????M3")

wildcard with positional characters

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

use tilde wildcard with countif


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.

=COUNTIF(B5:B13,"*D5*")

COUNTIF wildcard not working

  • 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.
=COUNTIF(B5:B13,"*"&D5&"*")

COUNTIF wildcard error - fixed!

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.

=COUNTIF(B5:B13,"SB*")

COUNTIF wildcard error with text

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

leading spaces in text

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

remove leading spaces

Read More: How to Use COUNTIF Function to Count Text from List in Excel


Similar Readings


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.

=COUNTIF(B5:B13,"11/?/2016")

COUNTIF wildcard with dates

  • 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.
=COUNTIFS(B5:B13,">="&DATE(2016,11,1),B5:B13,"<"&DATE(2016,12,1))

COUNTIFS with dates

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

=COUNTIF(B5:B13,*1*)

COUNTIF wildcard with numbers

Then the following error will occur.

countif wildcard with numbers not working

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.

=COUNTIF(B5:B13,"*1*")

Excel COUNTIF wildcard 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

Md. Shamim Reza
Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo