using countif and applying wildcards to a cell name

this is what I have typed in cell AP38:

In cells A36 and U45 it says abc123

in cell AC38 it says abc 123-1,2

I expected to get a return value of 1 in cell AP38. Instead it shows 0.

Troubleshooting, I changed the AP38 formula to:

I got a return value of 2, like i expected.
Why am I not getting the result I expect from using the wildcard? Everywhere I look online it shows to add a wildcard to a cell name in the format of "*"&cell&"*"


Who is Participating?
barry houdiniConnect With a Mentor Commented:
Hello dwils15,

Sorry, in the last formula that I suggested I used TRIM but that won't remove single internal spaces so that won't work. You would need this formula to give the result 4


see attached sheet 1

If you need to have the text in A1 as "abc 123-1,2" then I think you have a dilemna because in that case you aren't just ignoring additional text in B1:B4 but you also want to ignore part of the text in A1. You can do that but you need to have some rules - how much of A1 (or which part of A1) needs to be completely included in the text in B1:B4? In your example, assuming you want to match the first 6 non-space characters of A1 you could adjust my formula like this


see attached sheet 2

I assume that you are using simplified examples of the data. If the above doesn't work for you then it might be better if you can post the actual data, or a closer representation of it

regards, barry
You mention the value in U45, however, your formula references $U$50 -- was this just a typo in your question, or is this an actual error in your formula?
shouldn't cell AC38 say abc124 instead of "abc 123-1,2" ? Please note the space will affect comparison.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

sorry thats abc123. over here it works fine with the formula COUNTIF($A$36:$U$50,"*"&AC38&"*"), i think the problem is your not actually getting a match.
dwils15Author Commented:
sah. the value is in u45, but should a36:u50, include u45 in the search?

roger. cell ac38 has a space. "abc 123-1,2".
any cell in the range of a36:u50 can have abc123, or abc 123, yet I still dont get a count value returned in cell ap38.
I want the user to be able to put in either abc123, abc 123, abc123-1, abc 123-1, etc. in any cell from a36:u50.
Anytime some of the characters found in ac38 (abc12...) are entered in any cell(s) from a36:u50 I want the count of AP38 to increase.

dwils15Author Commented:
i copied that section to a new worksheet to make it easier to follow:

B2=abc 123
B4=abc 123-1
C1 VALUE = 2.

I want C1 value to equal 4 in this scenario.
barry houdiniCommented:
Roger is correct, the * wildcard represents any number of other characters (even zero) but for your formula to work the cells in the COUNTIF range must include at least the whole value in AC38, none of them contain "abc 123-1,2" in its entirety.

If you want to allow a space in the input cells then make AC38 just abc123 and then try this formula


regards, barry

Hello dwils,

your formula checks for * ac38value *, which means that for something to count it has to have the whole string: example "hello abc 123-1,2 there" will count as will "athisbc 123-1,2".

If it does not have the whole string, example "abc 1" or "abc 123", it will not count.

Change the value in ac38 to "abc123" and it should work.

barry houdiniCommented:
Our posts crossed. For that new setup try this version of my suggestion


regards, barry

[ignore previous version of formula - too many parentheses]

Yep, barry's formula is the way to go. The trim will remove any spaces when counting, so even "a b c 1 2 3" will count.

best of luck,
dwils15Author Commented:
In an effort to keep my post simple I only mentioned ac38, but in reality ac38 is just the first cell of a column of over 80 cells that are linked and updated by another spreadsheet. I was just going to copy the formula down the table. So changing the format of what is entered into AC38 isn't a viable option. I need the formula to determine what is in the AC cell and return anything from the A36:U50 range that matches with or without spacing and additional characters.
Sorry I didn't mention this in the beginning, but I was trying to keep it simple.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.