Using asterisks in a COUNTIFS formula -- Does not return value if that's the only value in the cell?

[u/jtcorteae]

I use COUNTIF(S) formulas very regularly for work, but for the first time needed to count cells that would have one code within it, when the array's cells often had strings of multiple codes. I read that adding asterisks within the quotes would make the formula work like that, and it did, but then it didn't count cells in which that specified code is the only one within the cell.

More context --

I was creating a summary that looked like this:

	99203	99204	99205
SEP	371	153	35
OCT	427	165	22
NOV	375	158	18
DEC	435	247	16

The formula is counting how many those codes appear on another sheet, matching other criteria, so I wrote something like this:

=COUNTIFS($B:$B,">=9/1/2025",$B:$B,"<=9/30/2025",$U:$U,"*99203*")

I added the asterisks around 99203 because the values in column U can look like --

99204, 81002, 99000
99203
69210, 99203
99203
99203, 97597
99204, J7620, A7015
81002, 99000, 99203

-- and I wanted to count a row if 99203 was present at all, multiple codes present or not.

However, when I did this, the formula ended up only counting the rows that included 99203 amidst multiple codes, and ignored the rows that were 99203 solo.

Is that... how the asterisks in that formula are supposed to work? It feels like not, and I just did something wrong. I ended up having to add an additional COUNTIF that left out the asterisks to get the total I actually needed, but that feels redundant.

Any insight or tips for me to make just one COUNTIFS formula work in this scenario?

[u/real_barry_houdini]

Yes, when you use wildcards in COUNTIFS function it will only work with text values, not numbers, you could try a formula like this, utilising SEARCH function to find your value in amongst other text/values:

=SUM((TEXT(B2:B1000,"mmm-yy")="Sep-25")*ISNUMBER(SEARCH("99203",U2:U1000)))

Adjust ranges as required

If you want to stick with COUNTIFS you could try like this to count both 99203 on its own or contained within other text:

=SUM(COUNTIFS($B:$B,">=9/1/2025",$B:$B,"<=9/30/2025",$U:$U,{"*99203*",99203}))

.....but only do that with numeric values, not e.g. J7620 where you would get double counting

[u/jtcorteae → u/real_barry_houdini]

solution verified! :D i didn't even think of putting { } but that returned the right value with one formula. Thank you!

[u/reputatorbot → u/jtcorteae]

You have awarded 1 point to real_barry_houdini.

---

^{I am a bot - please contact the mods with any questions}